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
|