I use 2 custom functions to parse out number/text from a string.
Press Alt + F11 to open the VBA module, click INSERT/NEW/MODULE and paste
the following 2 functions into the empty module.
Each function starts with 'Function and ends with 'End Function'
Function ExtractNumbers(varVal As Variant) As Long
Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String
Dim intChar As Integer
intLen = Len(varVal)
Application.Volatile
For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
intChar = Asc(strChar)
Select Case intChar
Case 1 To 47
Case 48 To 57
strVal = strVal & strChar
Case 58 To 255
Case Else
End Select
Next i
If Len(strVal) = 0 Then
ExtractNumbers = 0
Else
ExtractNumbers = CLng(strVal)
End If
End Function
Function ExtractAlpha(varVal As Variant) As String
Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String
intLen = Len(varVal)
For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
If Asc(strChar) >= 65 And Asc(strChar) <= 90 Or _
Asc(strChar) >= 97 And Asc(strChar) <= 122 Then
strVal = strVal & strChar
End If
Next i
Dear Mr. David,
I have tried but the results based on some specific condition of delimited
with any character such as commas, tabs or fixed numbers etc. Whereas, there
is no fixed no. of digits in the data I have. Need help.
Ron Rosenfeld showed you solutions using REGEX and Longre's free
morefunc.xll add-in
muddan madhu showed you the array formulae
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$20),1)),0),COUNT(1*MID(A1,ROW($1:$20),1)))
and
=MID(A1,ROW($1:$20),LEN(A1)-COUNT(1*MID(A1,ROW($1:$20),1)))
It's no good coming back and repeatedly asking the same question if you
don't explain what was wrong with the previous answers you've had.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.