Need help in MS Excel for extraction

Question

Need help to extract alphanumeric string from a cell IN EXCEL 2013/2016 Example cells:

Tom Jomes 10102230SP01001 Payment
Jonas Haul 550R202202230016 Car
Holland motors 10102230C002001
Jeevan 0073669900002 cc

Need below result in new cell against the above:

10102230SP01001
550R202202230016
10102230C002001
0073669900002

Thanks in advance

Questions that just state what you need are off-topic on SO. Please refer to [ask] a question with a [mcve]. Include the formula/VBA-code you tried and let us know what didn't work for debugging purposes. — JvdV, Mar 22 '22 at 08:14

score 4 · Accepted Answer · answered Mar 22 '22 at 08:24

4

From your current sample you may utilize FILTERXML() function.

=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[translate(.,'1234567890','')!=.]")

Details here from JvdV

answered Mar 22 '22 at 08:24

Harun24hr

3

Allow me to make one small addition `=SUBSTITUTE(FILTERXML("~~'"&SUBSTITUTE(A1," ","'")&"~~","//s[translate(.,'0123456789','')!=.]"),"'","")` – JvdV Mar 22 '22 at 08:36
3

Nice trick to make nodes as text instead of numbers. So, it will not loose starting zeros. – Harun24hr Mar 22 '22 at 09:12
1

Thank you @JvdV for you help in this matter. Great learning for me. – hardcrusher2 Mar 23 '22 at 16:35
Thank you @Harun24HR for you help in this matter. Yeah those starting zeroes were require. – hardcrusher2 Mar 23 '22 at 16:36
@hardcrusher2 So, did it work for you? If it helps then mark the answer as accepted (tick mark it). – Harun24hr Mar 24 '22 at 01:43

1 Answers1