Column Split by Last Delimiter.

New Contributor


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. 



5 Replies
best response confirmed by Murali369 (New Contributor)


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.


You may extract by

=FILTERXML("<t><s>" & SUBSTITUTE(A2,"-","</s><s>") &"</s></t>", "//s[last()]")




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


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


@Sergei Baklan 
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?