SOLVED

Column Split by Last Delimiter.

New Contributor

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. 

Murali369_0-1633352131760.png

 

5 Replies
best response confirmed by Murali369 (New Contributor)
Solution

@Murali369 

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.

@Murali369 

You may extract by

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

sample:

image.png

@Murali369 

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

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

www.000webhost.com