Extract Text and Numbers from Cell

[glaupie]

Hello there,

Our marketing department asked me to assist with SMS responses. Customers (who are clearly not very smart) are asked to SMS their ID Numbers and Names to a short code to enter a competition.

The data is a mess. Some have ID numbers first, others last or in the Middle. SOme have full names, or no names, or no ID numbers, or they reply "hello, my name is Maria and this is mt address..." - you get the idea.

Here are a few cells:

[TABLE="width: 260"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Nthabiseng 199401230585086[/TD]
[/TR]
[TR]
[TD]Miss ME Khumalo 8301170818086[/TD]
[/TR]
[TR]
[TD]%

P.XHANYWA8603261214087[/TD]
[/TR]
[TR]
[TD]Nthabiseng 9401230585086[/TD]
[/TR]
[TR]
[TD]Nthabiseng 9401230585086[/TD]
[/TR]
[TR]
[TD]Mìss L MOJA 8503101484082[/TD]
[/TR]
[TR]
[TD]T.P. Sikhakhane 7602290307087[/TD]
[/TR]
[TR]
[TD]My 7907210659081 my surname Mogajane motladi carron[/TD]
[/TR]
[TR]
[TD]ROCK YOLANDI 8304300045083[/TD]
[/TR]
[TR]
[TD]MATILDA NDLOVU ID 7501210515086[/TD]
[/TR]
[TR]
[TD]MATILDA NDLOVU ID 7501210515086[/TD]
[/TR]
[TR]
[TD]Eunice Jobson ID nom.8210120306086[/TD]
[/TR]
[TR]
[TD]E. M A R T H A S E G A K W E N G . 7808250789087[/TD]
[/TR]
[TR]
[TD]A.J. Pursent 8505251788089[/TD]
[/TR]
[TR]
[TD]Sipho david mabuza ID 8512015980085[/TD]
[/TR]
[TR]
[TD]RD.MASIPA 7703040567085[/TD]
[/TR]
[TR]
[TD]busisiwe violet mabena 7510070872081[/TD]
[/TR]
[TR]
[TD]E; P.P MTHEMBU& 8902110704086[/TD]
[/TR]
[TR]
[TD]Vidah,muguwelele,7104020105089[/TD]
[/TR]
[TR]
[TD]Dineo 8807250353083[/TD]
[/TR]
[TR]
[TD]P.E .TEMBE. 8004210289082.[/TD]
[/TR]
[TR]
[TD]P.E.ZULU.7710102163083


I need to extract the possible ID numbers to one cell, and the possible names to another (preferably using a formula and not VBA - I want the marketing guys to be able to do this themselves daily).

So far I have managed to extract all numbers in the cell (works pretty good, but not perfectly), using array formula
=MID(B2,MATCH(TRUE,ISNUMBER(1*MID(B2,ROW(B:B),1)),0),COUNT(1*MID(B2,ROW(B:B),1)))[/TD]
[/TR]
</tbody>[/TABLE]

For some reason the flipside of this (where I use ISTEXT and not ISNUMBER) does not work.
I also tried
=LEFT(B2,FIND("^^",SUBSTITUTE(" "&B2," ","^^",LEN(" "&B2)-LEN(SUBSTITUTE(" "&B2," ",""))))-1)
which SHOULD remove all numbers and keep text, but it doesn't work great (especially where there are commas or weird characters).

ANy ideas how I can do this in a better way?

Sorry for the long post

Gerhard

 

[Michael M]

Try this Function
Rick Rothstein did the base code and the sample was from Jerry Beaucaire....the Rocket Scientist
Copy the code into a Standard Module in VBE and then use

=Clall(A1)

and copy down as far as required

Function Clall(ByVal txt As String) As String
Dim X As Long     'Code base by Rick Rothstein (MVP - Excel), This sample by Jerry Beaucaire
    For X = 1 To Len(txt)
        If Mid(txt, X, 1) Like "*[!0-9]*" Then Mid(txt, X, 1) = Chr(1) ' Leave only numbers
 '       If Mid(txt, X, 1) Like "*[!A-Za-z ]*" Then Mid(txt, X, 1) = Chr(1) ' Leave only letters and spaces
'        If Mid(Txt, X, 1) Like "*[!A-Za-z]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only letters
    Next
Clall = Replace(txt, Chr(1), "")
End Function

 

[mikerickson]

You could put this in C1 to extract the numeric portion of B1

=MID(B1, MIN(FIND({1,2,3,4,5,6,7,8,9,0}, B1& "1234567890")), COUNT(1*MID(B1, ROW($1:$255),1)))

and then =SUBSTITUTE(A1, C1, "") would return the text portion.

 

[glaupie]

You could put this in C1 to extract the numeric portion of B1

=MID(B1, MIN(FIND({1,2,3,4,5,6,7,8,9,0}, B1& "1234567890")), COUNT(1*MID(B1, ROW($1:$255),1)))

and then =SUBSTITUTE(A1, C1, "") would return the text portion.

Thanks!

I used this, and both seem to work:
=TEXT(LOOKUP(9.99E+307,--MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&1234567890)),ROW(INDIRECT("1:"&LEN(B2))))),0)

 

[glaupie]

Any idea how to change that so that it works on this cell:
I want to get the ID Number, not the first "2".
2 matjila mp 7310310472085

Can we maybe change the formula to ignore the 2 because its length is too short and move on to the next set of number? Or is that too much to ask?

 

[glaupie]

Currently this formual returns the first set of numbers in the cell. Is there any way that I can change it to return the first set of numbers that contain 5 or more characters.

=IFERROR(TEXT(LOOKUP(9.99E+307,--MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&1234567890)),ROW(INDIRECT("1:"&LEN(B2))))),0),"")

 

[mikerickson]

The CSE formula should work
=MAX(IFERROR(MID(A1,COLUMN(A1:AZ1),ROW(A1:A255))+0,0))