Extract Text Elements in a Cell providing stnd Starting/Stopping point.

**Miskondukt** · 02-13-2014, 02:19 PM

Hello again,

I have a cell that may contain, "ONT EMS Asbestos Testing for job #XXXXX, ClientName," that I want the #xxxxx dropping into another cell.

I am trying to extract only the numerals after the pound sign/hash-tag (#). Is there a string of functions to do this?

Thank you

**AlKey** · 02-13-2014, 02:31 PM

Try this

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","#"),"#",REPT(" ",255)),255,255))

	A	B
1	ONT EMS Asbestos Testing for job #XXXXX, ClientName	XXXXX

**Miskondukt** · 02-13-2014, 02:41 PM

That did it perfectly. Thank you. Added rep.

However, can you edumacate me a bit on that string's mechanics so I can (in the future) write my own?

**AlKey** · 02-13-2014, 02:48 PM

Thank you for the feedback!

This formula is looking to find "#" anywhere in the string and will count it as a starting point. However, it is the only character in the string. So, formula also replaces "," with another "#" which is the end point.

hope this help.

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).