Extracting alphanumeric data

[beepetark]

Excel Guru and Excel expertise I like to ask that I am having alphanumeric data so I want to extract alphabets in one row and numeric in another group example
abf145tf=abftf 145
ghj75kl=ghjkl 75

Please hoping answers from excel gurus

 

[James006]

Hi,

You can use a couple of UDF's ...

Function TextOnly(mystr As Range) As String
Dim i As Integer
Dim xValue As String
Dim OutValue As String
xValue = mystr.Value
For i = 1 To VBA.Len(xValue)
    If Not VBA.IsNumeric(VBA.Mid(xValue, i, 1)) Then
        OutValue = OutValue & VBA.Mid(xValue, i, 1)
    End If
Next i
TextOnly = OutValue
End Function




Function NumericOnly(mystr As Range)
Dim myOutput As String, i As Integer
    For i = 1 To Len(mystr)
        If IsNumeric(Mid(mystr, i, 1)) Then
            myOutput = myOutput & Mid(mystr, i, 1)
        End If
    Next i
NumericOnly = myOutput * 1
End Function

Hope this will help

 

[beepetark]

I want using functions and formulae please

 

[DHayes]

There was a same question earlier today. give this a try.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")&" "&SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

 

[Yongle]

Regex alternative

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#C6E0B4]B1AHF3|B1AHF3_:P00387-2\NB5R3_P00387/NB5R3_!"£$%^&*(){}[]_+-=@~'#,.:;[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Result[/td][td] Formula[/td][td] Explanation[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]BAHFBAHFPNBRPNBR[/td][td] =only(A2,0)[/td][td] 0 = ALPHA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]1313003872530038753[/td][td] =only(A2,1)[/td][td] 1 = NUMERICS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]13|13_:00387-2\53_00387/53_!"£$%^&*(){}[]_+-=@~'#,.:;[/td][td] =only(A2,2)[/td][td] 2 = NON ALPHA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]BAHF|BAHF_:P-\NBR_P/NBR_!"£$%^&*(){}[]_+-=@~'#,.:;[/td][td] =only(A2,3)[/td][td] 3 = NON NUMERICS[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

Here is the UDF to use (code goes in in standard module)

Function Only(strIn As String, Optional P As Integer) As String
    Dim pStr As String
    Select Case P
        Case 0: pStr = "[^a-zA-Z]+"
        Case 1: pStr = "[^\d]+"
        Case 2: pStr = "[a-zA-Z]+"
        Case 3: pStr = "[\d]+"
        Case Else: pStr = "[.]+"
    End Select
    With CreateObject("vbscript.regexp")
     .Global = True
     .Pattern = pStr
    Only = .Replace(strIn, vbNullString)
    End With
End Function

I want using functions and formulae please

@★ beepetark Posted for the benefit of others who may also be reading this thread

 

[pgc01]

Hi
Welcome to the board

If you have a recent version of excel you can use the function TextJoin().

Try (not tested):

For the digits: =TEXTJOIN("",TRUE,IFERROR(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")

For the non digits: =TEXTJOIN("",TRUE,IF(ISERROR(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

 

[beepetark]

Thank you for your concern
Mine is excel 2007
That is the problem

 

[James006]

With Excel 2007 and no UDF's ...

See formula from DHayes in message # 4 ...

 

[beepetark]

Mine is Excel 2007

 

[Yongle]

This only works with pattern Alpha followed by Numeric followed by Alpha as in example provided

with value in A1

formula in B1
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},""))))
Formula in C1
=SUBSTITUTE(A1,B1,"")