Help with extracting Mobile # from a string of text ?

[Maverick27]

Hi

Can someone share an Excel Function or Macro Code to extract the 2nd set of Tel/Mob # from a string of Text ?

I've managed to extract the 1st Set of Mob #s ( Col B ) using Function below:

=LEFT(B2,(FIND(" ",B2,1)-1))

I'm struggling with the 2nd set of #'s since the data in the Column is not consistent.

The final result need not be a 100% accurate, but somewhat sensible.

Rgds

 

[Kerryx]

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,":","")))/LEN(":")>1,RIGHT(A1,LEN(A1)-FIND(":",A1,3)+2),"")

 

[Kerryx]

or else just use text to columns and set the comma as your delimiter should get you most of the way there

 

[Maverick27]

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,":","")))/LEN(":")>1,RIGHT(A1,LEN(A1)-FIND(":",A1,3)+2),"")

Thanks Kerry.

Works like a charm.