CELL CONTENTS SEPARATION

T

Tazeem

Is there any shortcut to separate (text and number - like ABCDE123456) of a
single cell?. Especially if the no. of digits are not constant. Thanks
 
K

Kevin B

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

ExtractAlpha = strVal

End Function

Hope this helps...
 
T

Tazeem

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.

Regards,
 
D

David Biddulph

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.
 
Top