Extract Specific word from the String

[arpanpanchal]

Hi Friends

I have attached the example file. I want to extract specific text from the value in cell.

For example, I want to extract G001 from cell A2, G005A from cell A3 etc.

Is there any way someone can assist?

Many thanks in advance

[AliGW]

Is it always the last but one item in the string?

[Bo_Ry]

Please try at B2

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",20)),40),20))

[arpanpanchal]

Thanks but this doesnt work with my actual text string which is "Shop 1 - G001 - Moonview Shopping Centre".

[arpanpanchal]

Yes it will be there in the text string as per my example file attached.

[Bo_Ry]

Just increse the number
=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),200),100))

[menem]

Did your text have any more pattern explaination ?

Like

prefix - XXXX - suffix

while prefix and suffix can contain a "-" (or not)

By the way, Bo_Ry's formula can already extract your desired text form your sample file.


Regards.

[arpanpanchal]

This is perfect. Thanks a lot. It works perfectly.