Forum Discussion
Solved
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.
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
