Extract text only within a string, and numbers only within a string.

[archilles89]

Hi,

I have a jumble of text string where in one column I have to extract the text only (no numbers and special characters) while on the other column I have to extract numbers only (no text and special characters)

[TABLE="width: 500"]
<tbody>[TR]
[TD]String[/TD]
[TD]Text[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]aaabbb!.,'1234222[/TD]
[TD]aaabbb[/TD]
[TD]1234222[/TD]
[/TR]
[TR]
[TD]abc@gmail.com[/TD]
[TD]abcgmailcom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*82.\3246"5656)[/TD]
[TD][/TD]
[TD]8232465656[/TD]
[/TR]
</tbody>[/TABLE]

Can someone help me derive a formula for this?

Currently I am using this

=IF(OR(LEN(A2)>40,ISTEXT(FIND({"1","2","3","4","5","6","7","8","9","0","!","@","'","""","]","[","}","{","|","&","$","#","^","~","%","®","-",""},A2))),"Bad","Good")

and manually CTRL+F them and replace them all. Kinda hard.

Any help would be awesome thank you!

 

[pvr928]

Hi archilles89

If you're willing to use a Function and regular expressions, try these out. There is a separate function to extract text and numbers.

Function ExtractTextOnly(Str As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Dim Match As Variant
Dim matches As Variant
Dim sResult
With regex
  .Pattern = "[a-zA-Z]+"
  .Global = True
End With
         
Set matches = regex.Execute(Str)
     
For Each Match In matches
    sResult = sResult & Match.Value
Next Match
ExtractTextOnly = sResult
End Function


Function ExtractNumbersOnly(Str As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Dim Match As Variant
Dim matches As Variant
Dim sResult
With regex
  .Pattern = "[0-9]+"
  .Global = True
End With
         
Set matches = regex.Execute(Str)
     
For Each Match In matches
    sResult = sResult & Match.Value
Next Match
ExtractNumbersOnly = sResult
End Function

Once you have these in a Module, use them like any other Excel function, ie with the subject string in cell A1, place the function in cell B1 as required thus:

=ExtractNumbersOnly(A1)

Cheers

pvr928

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use for either retrieving text or digits...

Function Xtract(ByVal S As String, What As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If UCase(What) = "TEXT" Then
      If Mid(S, X, 1) Like "[!A-Za-z]" Then Mid(S, X) = " "
    ElseIf UCase(What) = "DIGITS" Then
      If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
    Else
      Mid(S, X) = " "
    End If
  Next
  Xtract = Replace(S, " ", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, to retrieve the text,

=Xtract(A1,"Text")

To retrieve the digits only...

=Xtract(A1,"Digits")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[archilles89]

Hi archilles89

If you're willing to use a Function and regular expressions, try these out. There is a separate function to extract text and numbers.

Function ExtractTextOnly(Str As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Dim Match As Variant
Dim matches As Variant
Dim sResult
With regex
  .Pattern = "[a-zA-Z]+"
  .Global = True
End With
         
Set matches = regex.Execute(Str)
     
For Each Match In matches
    sResult = sResult & Match.Value
Next Match
ExtractTextOnly = sResult
End Function


Function ExtractNumbersOnly(Str As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Dim Match As Variant
Dim matches As Variant
Dim sResult
With regex
  .Pattern = "[0-9]+"
  .Global = True
End With
         
Set matches = regex.Execute(Str)
     
For Each Match In matches
    sResult = sResult & Match.Value
Next Match
ExtractNumbersOnly = sResult
End Function

Once you have these in a Module, use them like any other Excel function, ie with the subject string in cell A1, place the function in cell B1 as required thus:

=ExtractNumbersOnly(A1)

Cheers

pvr928

Here is a UDF (user defined function) that you can use for either retrieving text or digits...

Function Xtract(ByVal S As String, What As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If UCase(What) = "TEXT" Then
      If Mid(S, X, 1) Like "[!A-Za-z]" Then Mid(S, X) = " "
    ElseIf UCase(What) = "DIGITS" Then
      If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
    Else
      Mid(S, X) = " "
    End If
  Next
  Xtract = Replace(S, " ", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, to retrieve the text,

=Xtract(A1,"Text")

To retrieve the digits only...

=Xtract(A1,"Digits")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you guys, both of these work!

Do I have to insert this to all my excel files? I tried it on other spreadsheets and it didn't work.

 

[Rick Rothstein]

Do I have to insert this to all my excel files? I tried it on other spreadsheets and it didn't work.

The code must be available in the workbook in order to use it, so yes, you have to install the code in any workbook that you want this function to be able to be used in. You could add the code to a blank workbook and then save that workbook as a Template, then when you open a New workbook, you could select your template and the function would automatically be available for use.

 

[archilles89]

The code must be available in the workbook in order to use it, so yes, you have to install the code in any workbook that you want this function to be able to be used in. You could add the code to a blank workbook and then save that workbook as a Template, then when you open a New workbook, you could select your template and the function would automatically be available for use.

Is there a way to add space? I accidentally left it out. Apparently I need spaces to remain. So for example "43Robert Gill99" will be converted into just "Robert Gill" including any spaces.

 

[Rick Rothstein]

Is there a way to add space? I accidentally left it out. Apparently I need spaces to remain. So for example "43Robert Gill99" will be converted into just "Robert Gill" including any spaces.

Here is my code modified to allow the spaces to remain if the What option is set to "Text"...

Function Xtract(ByVal S As String, What As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If UCase(What) = "TEXT" Then
      If Mid(S, X, 1) Like "[!A-Za-z ]" Then Mid(S, X) = " "
    ElseIf UCase(What) = "DIGITS" Then
      If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
    Else
      Mid(S, X) = " "
    End If
  Next
  If UCase(What) = "TEXT" Then
    Xtract = Application.Trim(S)
  Else
    Xtract = Replace(S, " ", "")
  End If
End Function