extract numbers from alphanumeric text string if 1st tier not available

**Ellice16** · 03-24-2013, 10:42 PM

I have this from my extract from accounting system.

4010400 · Sales - Amkt Svc:4010450 · Sales Service 3rd party - MSA:4010452 · WV - Service Contract (NSB) 954,215.44
5010101 · 3rd Party COS - Systems 48,423,699.30

I know if there is only first tier of account numbers I can set with LEFT(A2,7) easily to get 5010101.
How can I set a formula to extract the last tier of account number in that first example line to get result 4010452?

Ie. result shown to be :-
Column A Column B
4010452 954,215.44
5010101 48423699.30

**martindwilson** · 03-24-2013, 10:52 PM

text number is
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",25)),20))
to get a real number
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",25)),20))+0

**icestationzbra** · 03-24-2013, 11:16 PM

see if this is what you want...

**Ellice16** · 03-25-2013, 01:52 AM

Thanks Ice! It worked perfectly except on this line item :1010100 · Cash and cash equivalents:Petty Cash
where resulted in #N/A. Can you guide me how to decipher formula so I can apply to similar situations in future? Thanks a million!