Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
Text File | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
how to save a file as ASCII: expanded comma delimited text (.exp). | Excel Discussion (Misc queries) | |||
Convert text file to MS_Excel | Excel Discussion (Misc queries) |