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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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.· 10 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.· 3 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.· 1 years 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.· 1 years agoPerfect, thanks!
To post as a guest, your comment is unpublished.· 1 years agomany thanks!
To post as a guest, your comment is unpublished.· 3 years agoYes.. Its worked Dude! Thanks!