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
=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))
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 |
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
@★ beepetark Posted for the benefit of others who may also be reading this threadI want using functions and formulae please