Skip to main content

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.

Extract text only from alphanumeric string by using Defined Function in Excel

Extract text only from alphanumeric string with Kutools for Excel


arrow blue right bubble Extract text only from alphanumeric string by using Defined Function in Excel

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.


arrow blue right bubble Extract text only from alphanumeric string with Kutools for Excel

Kutools for Excel's Remove Characters feature also can help you extract text only in Excel.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

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.

For more about Remove Characters.


Relative Articles:

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns     Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
rajesh kumar bind · Its working bro.. thanks
This comment was minimized by the moderator on the site
Brilliant - Many Thanks - also works for Number Extraction if the 'NOT' statement is removed - :-)
This comment was minimized by the moderator on the site
=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
This comment was minimized by the moderator on the site
If 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..
This comment was minimized by the moderator on the site
Perfect, thanks!
This comment was minimized by the moderator on the site
many thanks!
This comment was minimized by the moderator on the site
Yes.. Its worked Dude! Thanks!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations