How to extract text only from alphanumeric string in Excel?
When you have a long list of multiple alphanumeric string in each cell in Excel, and you just want to extract only text strings from them, you can do as following ways I introduce.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Use Defined Function to extract text only from alphanumeric string in Excel by doing as follow:
1. Select a cell of the column you want to select and press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the pop-up window, click Insert > Module, then paste the following VBA code into the module.
VBA: Extract text only
Function TextOnly(pWorkRng As Range) As String 'Updateby20140515 Dim xValue As String Dim OutValue As String xValue = pWorkRng.Value For xIndex = 1 To VBA.Len(xValue) If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then OutValue = OutValue & VBA.Mid(xValue, xIndex, 1) End If Next TextOnly = OutValue End Function
3. Save the code and close the window, then type this formula =TextOnly(A1) (A1 is the first row cell in your list range you want to extract text only from) into a blank cell, press Enter button, then fill the handle to the range you want.
Kutools for Excel's Remove Characters feature also can help you extract text only in Excel.
1. Select the range you want to extract text only, and click Kutools > Text Tools > Remove Characters. See screenshot:
2. In the Remove Characters dialog, check Numeric and Non-printing check boxes. See screenshot:
3. Click Ok, then all the non-text characters are removed, see screenshot:
Tip: Because this applying will change your original data, you’d better to copy the original data to another place first.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agorajesh kumar bind · Its working bro.. thanks
To post as a guest, your comment is unpublished.· 2 years agoBrilliant - Many Thanks - also works for Number Extraction if the 'NOT' statement is removed - :-)
To post as a guest, your comment is unpublished.· 2 years ago=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")
use this formula.
if it is working please let me know on my e-mail firstname.lastname@example.org
To post as a guest, your comment is unpublished.· 7 months agoIf we want to extract only number then what should be the formula...above extract only alpha which great work..please let me know the solution for numeric and special character extraction..
To post as a guest, your comment is unpublished.· 11 months agoPerfect, thanks!
To post as a guest, your comment is unpublished.· 11 months agomany thanks!
To post as a guest, your comment is unpublished.· 2 years agoYes.. Its worked Dude! Thanks!