Extracting Number (Numerical) and Text (Textual) Portions Of An Alphanumeric String Into Separate Columns

[Carkcark]

I have a column in an export of data which contains flight numbers which vary between 5 and 6 characters long. These alphanumeric strings can be in a varied format in that sometimes they are:

XX###
XX###A
XXX###
XXX##A

I have a function to allow me to keep just the text portion of the string which works beautifully, however after scouring the internet for a while I haven't been able to find an equivalent for the numerical portion.

Function TextOnly(pWorkRng As Range) As String'Updateby20140515
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
    If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
        OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
    End If
Next
TextOnly = OutValue
End Function

Ideally I would like to keep it as a function, but I will consider using formulae as well although I had an issue whilst using the array formula from exceltools as it was giving me a NUM error which I couldn't seem to resolve:

[B]=SUMPRODUCT(MID(0&A2,LA[/B][B]RG[/B][B]E(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)[/B]

 

[Rick Rothstein]

Here is a different way to write your TextOnly function and, using the same structure, I have created the DigitsOnly function you requested...

[table="width: 500"]
[tr]
	[td]Function TextOnly(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[A-Za-z]" Then TextOnly = TextOnly & Mid(S, X, 1)
  Next
End Function[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td]Function DigitsOnly(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[0-9]" Then DigitsOnly = DigitsOnly & Mid(S, X, 1)
  Next
End Function[/td]
[/tr]
[/table]

 

[István Hirsch]

If you need a formula:

=SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),5),"A","")