Ascii iinput file - separate text from Numbers
 

I have an input file that I would like to get into separate cells. The
data is something like this:

George Bush123456-78902
Abe Lincoln9876-543210
May Lou Rettin76543-2109713

I don't care about separating the first and Last name. What I need is
to determine when the number begins. Any ideas?

Thanks!!

Ascii iinput file - separate text from Numbers
 

Kathy,

Don't know if this is the best way, but try this UDF. Put it in a regular
module in the workbook which contains the data you are trying to parse (Alt
F11, Insert Module, paste function below).

In a cell type: =GetNumericOnly(A1) where A1 = 1st cell of the data to parse.

'=====================================
Function GetNumericOnly(ByVal rng As Range)
On Error GoTo ErrorHandler
ReDim aArray(0)
sValue = rng
iLen = Len(rng)
d = 1
For C = 1 To iLen
sItem = Mid(sValue, C, 1)
If sItem Like "[0-9]" Then GoTo StoreValue
If sItem Like "-" Then GoTo StoreValue
GoTo NextItem
StoreValue:
ReDim Preserve aArray(d)
aArray(d) = sItem
d = d + 1
NextItem:
Next
For e = 1 To d - 1
sNewItem = sNewItem & aArray(e)
Next
Erase aArray
If sNewItem = "" Then GoTo ErrorHandler
GetNumericOnly = sNewItem
Exit Function
'---------------------
ErrorHandler:
GetNumericOnly = "#N/A"
End Function
'=====================================

Hope it helps. Good Luck.

MSweetG222

Ascii iinput file - separate text from Numbers
 

Is it always letters then finishing off with numbers in each string? if so

=MID(A1,MIN(IF(ISERR(FIND({1,2,3,4,5,6,7,8,9,0},A1 )),"",FIND({1,2,3,4,5,6,7,8,9,0},A1))),255)

will return the string from the first number to the end
Where Bush would be in A1, assume that you put the above formula in B1, then
in C1

=SUBSTITUTE(A1,B1,"")

will give you the name

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


wrote in message
oups.com...
I have an input file that I would like to get into separate cells. The
data is something like this:

George Bush123456-78902
Abe Lincoln9876-543210
May Lou Rettin76543-2109713

I don't care about separating the first and Last name. What I need is
to determine when the number begins. Any ideas?

Thanks!!

Ascii iinput file - separate text from Numbers
 

Thanks - this worked perfectly!!!
MSweetG222 wrote:
Kathy,

Don't know if this is the best way, but try this UDF. Put it in a regular
module in the workbook which contains the data you are trying to parse (Alt
F11, Insert Module, paste function below).

In a cell type: =GetNumericOnly(A1) where A1 = 1st cell of the data to parse.

'=====================================
Function GetNumericOnly(ByVal rng As Range)
On Error GoTo ErrorHandler
ReDim aArray(0)
sValue = rng
iLen = Len(rng)
d = 1
For C = 1 To iLen
sItem = Mid(sValue, C, 1)
If sItem Like "[0-9]" Then GoTo StoreValue
If sItem Like "-" Then GoTo StoreValue
GoTo NextItem
StoreValue:
ReDim Preserve aArray(d)
aArray(d) = sItem
d = d + 1
NextItem:
Next
For e = 1 To d - 1
sNewItem = sNewItem & aArray(e)
Next
Erase aArray
If sNewItem = "" Then GoTo ErrorHandler
GetNumericOnly = sNewItem
Exit Function
'---------------------
ErrorHandler:
GetNumericOnly = "#N/A"
End Function
'=====================================

Hope it helps. Good Luck.

MSweetG222