Forum Discussion
Column Split by Last Delimiter.
Hi,
I need to split column by last delimiter. Pls see the below example. Any help is highly appreciable. Thanks.
I need the last set of my Project numbers which has no fixed length.
Let's say the values are in A2 and down.
Enter the following formula in another cell in row 2, for example in B2:
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",255)),255))Fill down.
5 Replies
You may extract by
=FILTERXML("<t><s>" & SUBSTITUTE(A2,"-","</s><s>") &"</s></t>", "//s[last()]")sample:
SergeiBaklan
Hi, I've just come across your formula and it is exactly what I need for one piece of work. Thanks! My next questions are 1)could you explain the components so I understand? and 2) How could I change it for prefix?
Let's say the values are in A2 and down.
Enter the following formula in another cell in row 2, for example in B2:
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",255)),255))Fill down.
- Perfectly working but I need to understand this logic. thanks.
REPT(" ",255) returns a string of 255 spaces.
SUBSTITUTE(A2,"-",REPT(" ",255)) replaces every hyphen "-" in the string with 255 consecutive spaces.
So for example
A-B - 1234
becomes
A ... B ... 1234
where ... stands for lots of spaces.
RIGHT(SUBSTITUTE(A2,"-",REPT(" ",255)),255) takes the last 255 characters of this string. In the above example:
... 1234
Finally, TRIM removes the leading (and trailing) spaces, so
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",255)),255))
leaves only
1234
