Return a specific string in text without using text functions.

[wdguitar]

For automation purposes, I am trying to extract an identifying number from a cell that has multiple, varying characters on either side (hence why no left or right, unless you have a fancy way to use them). Workbook attached

[FlameRetired]

Hello wdguitar. Welcome to the forum.

Try this:

Please Login or Register  to view this content.

[TMS]

One way:

Please Login or Register  to view this content.

[FlameRetired]

Here's another way if that space character following the 'target' string is always the 3rd space.

Please Login or Register  to view this content.

[TMS]

@FR: quicker and neater . I ALWAYS forget that approach and go back to my first principles. One day it will sink in .

[FlameRetired]

@ TMS

Yeah I feel your pain. These are often tempting to play with off screen.

[wdguitar]

This worked great! Thank you so much!

[Rick Rothstein]

I think this formula should also work...

Please Login or Register  to view this content.

[TMS]

@Rick: concatenating a potentially missing value to the end of the string to avoid the FIND failing … yet another approach that doesn't automatically come to mind. Must try to improve my memory!

[menem]

This may be work also.

In B2
=trim(mid(substitute(a2&" "," ",rept(" ",len(a2)+1)),(len(a2)+1)*2+find("#",a2)-1,len(a2)))

2 from 2 spaces before # sign.

Regards.