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

archilles89

New Member
Joined
Jun 3, 2018
Messages
5
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)

StringTextNumber
aaabbb!.,'1234222aaabbb1234222
abc@gmail.comabcgmailcom
*82.\3246"5656)8232465656

<tbody>
</tbody>

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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.

Code:
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:

Code:
=ExtractNumbersOnly(A1)

Cheers

pvr928
 
Last edited:
Upvote 0
Here is a UDF (user defined function) that you can use for either retrieving text or digits...
Code:
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.
 
Upvote 0
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.

Code:
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:

Code:
=ExtractNumbersOnly(A1)

Cheers

pvr928

Here is a UDF (user defined function) that you can use for either retrieving text or digits...
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"...
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top