separate numbers from text

[lezawang]

Hi
is it possible to separate text from number if the test is like this

12344AAAAAAAAA12333333AAAAAAA

or

BBBBBBB12344BBB123BBBBBBB211BBBBB122

in summery text and number together, no space, no - or , or tab etc.

Thanks

 

[lrobbo314]

What is the result that you are looking to get from the data that you posted?

If you have 12344AAAAAAAAA12333333AAAAAAA, are you looking to get something like 1234412333333AAAAAAAAAAAAAAAA?

 

[lezawang]

Thanks, yes that is one or just put the text in one column and the number in another column.

 

[Nishant94]

If you have Excel 2016 then you can use this:

Excel 2016
	A	B	C
1	12344AAAAAAAAA12333333AAAAAAA	1234412333333	AAAAAAAAAAAAAAAA
2	BBBBBBB12344BBB123BBBBBBB211BBBBB122	12344123211122	BBBBBBBBBBBBBBBBBBBBBB
Sheet17

Cell Formulas
Range	Formula
B1	{=TEXTJOIN("",,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/10)*10,""))}
C1	{=TEXTJOIN("",,IF(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("65:90")),0)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[lrobbo314]

If you don't have the TextJoin function available, here is some code that will do the trick. This assumes that your data is in column A starting in A1. I commented the code with an alternative that allows you to just select the range manually. This will output the results in the 2 columns to the right of your original column. Let me know if you have any questions.

Sub SeparateAlphaNum()
Dim r As Range
Dim AR()
Dim res()
Dim tmp As String
Dim num As String
Dim alpha As String


Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row()) 'Change references to where your data is
'set r = application.InputBox("Select Range","Excel",,,,,,8) 'You can use this to select the range manually
AR = r.Value
ReDim res(1 To UBound(AR), 1 To 2)


For i = 1 To UBound(AR)
    For j = 1 To Len(AR(i, 1))
        tmp = Mid(AR(i, 1), j, 1)
        If IsNumeric(tmp) Then
            num = num & tmp
        Else
            alpha = alpha & tmp
        End If
    Next j
    res(i, 1) = "'" & num
    res(i, 2) = alpha
    tmp = vbNullString
    alpha = vbNullString
    num = vbNullString
Next i


Set r = r.Cells(1, 1).Offset(, 1)
Set r = r.Resize(UBound(res), UBound(res, 2))
r.Value = res()


End Sub

 

[lrobbo314]

Or... Here are two functions that you can use to do the same thing.

Function SeparateNum(s As String) As String
Dim num As String
Dim n As String
For i = 1 To Len(s)
    n = Mid(s, i, 1)
    If IsNumeric(n) Then
        num = num & n
    End If
Next i
SeparateNum = num
End Function


Function SeparateAlpha(s As String) As String
Dim alpha As String
Dim n As String
For i = 1 To Len(s)
    n = Mid(s, i, 1)
    If Not IsNumeric(n) Then
        alpha = alpha & n
    End If
Next i
SeparateAlpha = alpha
End Function

After pasting the code, in your worksheet you would use the functions like, =SeparateNum(A1) and =SeparateAlpha(A1).