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

	A​	B​	C​
1​
2​	B1AHF3|B1AHF3_:P00387-2\NB5R3_P00387/NB5R3_!"£$%^&*(){}[]_+-=@~'#,.:;
3​
4​	Result	Formula	Explanation
5​	BAHFBAHFPNBRPNBR	=only(A2,0)	0 = ALPHA
6​	1313003872530038753	=only(A2,1)	1 = NUMERICS
7​	13|13_:00387-2\53_00387/53_!"£$%^&*(){}[]_+-=@~'#,.:;	=only(A2,2)	2 = NON ALPHA
8​	BAHF|BAHF_:P-\NBR_P/NBR_!"£$%^&*(){}[]_+-=@~'#,.:;	=only(A2,3)	3 = NON NUMERICS

Sheet: Sheet2

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,"")