Delimiting to extract the last field from the right of string

[namy77]

Good morning,

I have strings with components separated by "-" and they have different numbers of these components, but I am most interested in the far right component.

So for example:

ABC-123-FG-34-GHJ-55
ABF-12-FF-47
HJG-92-BB-4B-88

to be delimited to

55
47
88

Thanks!

[Phuocam]

Try this ...

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))

Or:

=LOOKUP(10^11,-RIGHT(A1,{1,2,3,4,5,6,7,8,9,10}))

[namy77]

That was amazing... how?

[davsth]

in your example right(a1,2) would work for all examples you have shown.

[namy77]

It did - I just don't understand how that formula works...

[AlKey]

Here is another one

Please Login or Register  to view this content.

v	A	B
1	ABC-123-FG-34-GHJ-55	55
2	ABF-12-FF-47	47
3	HJG-92-BB-4B-88	88

[namy77]

Thanks so much!! I have another question

How would I be able query the second to last element? So:

ABC-123-FG-34-GHJ-55
ABF-12-FF-47
HJG-92-BB-4B-88

Will yield:

GHJ
FF
48

Thanks!

[AlKey]

Try this
Enter in B1 and copy down

Please Login or Register  to view this content.

v	A	B
1	ABC-123-FG-34-GHJ-55	GHJ
2	ABF-12-FF-47	FF
3	HJG-92-BB-4B-88	4B