How to Extract Text Only from Alphanumeric String in Excel?
Last Updated :
06 Dec, 2023
In the vast landscape of Excel spreadsheets, managing data efficiently is crucial for streamlined analysis and reporting. Often, one encounters the challenge of dealing with cells containing a mix of letters and numbers, commonly known as alphanumeric strings. Extracting only the text component from such strings becomes essential for various data manipulation tasks. In this article, we will explore practical methods to effortlessly extract text-only portions from alphanumeric strings in Excel. Whether you’re a seasoned Excel user or just embarking on your spreadsheet journey, mastering these techniques will empower you to wrangle your data with precision and ease. Join us as we unravel the secrets to extracting meaningful text from the alphanumeric complexity, making your Excel experience more efficient and tailored to your specific needs.
What is a Substring in Excel?
To put it simply, it’s a text entry. In this article, we will be using various approaches to extract text from Alphanumeric strings.
How to Extract Substring of a Certain Length
Three distinct functions are available in Microsoft Excel to extract text from a cell that is a certain length. Use one of these formulas based on where you want to begin the extraction.
- LEFT function – to take the leftmost substring and extract it
- RIGHT function – to take the rightmost substring and extract it
- MID function – to begin extracting a substring from the centre of a text string at the specified position
Extract Substring from Start of String (LEFT)
Using the Excel LEFT function, we can extract text from the left of a string.
Syntax
=LEFT(text, [num_chars])
Here, Text is the cell’s address where the source string is located and The amount of characters you want to extract is num_chars.
E.g. To select the first 5 characters from the start of the string, use:
=LEFT(A2,5)
Get Substring from End of String (RIGHT)
Use the Excel RIGHT function to extract a substring from the end of a string
Syntax
=RIGHT(text, [num_chars])
E.g. To select the last 5 characters from the end of the string, use:
=RIGHT(A2,5)
Extract text from Middle of String (MID)
MID is the function to use if you want to extract a substring that begins in the centre of a string at the place you provide.
Syntax
=MID(text, start_num, num_chars)
Here, apart from the aforementioned elements, start_num indicates the starting point
E.g. To select 2 characters from the middle of the string starting at the 5th character, use:
=MID(A2,5,2)

Most often, we receive “ID fields” as alphanumeric strings. Suppose we have to extract only text from “ID fields” for further process.
Let’s explore different approaches to extract text from an alphanumeric string in Excel.
Approach 1. Using Excel Formula
The above-discussed problem statement can be resolved using inbuilt Excel formulas, as discussed below:
Syntax:
=SUBSTITUTE (text, old_text, new_text, [instance_num])
Where,
- text: It is the input cell (text).
- old_text: It is the text to find.
- new_text: It is the text to replace.
Step 1. Open Excel.
Step 2. Type any alphanumeric string in cell “B5” (e.g. geeksId345768).

Step 3. Write below formula in cell “C5” (we used nested substitute function to replace numbers 0 to 9)
= SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B5,”0″,””),”1″,””),”2″,””), “3”,””),”4″,””),”5″,””), “6”,””),”7″,””),”8″,””),”9″,””)

Approach 2. Excel VBA User-Defined Function
In Excel VBA we can define functions to resolve the problem statement. An implementation is given below for the same.
Step 1. Open Excel.
Step 2. Type any alphanumeric string in cell “B5” (e.g. geeksId345768).

Step 3. Write below VBA code in the module:
Function onlyText(rg As Range) As String
Dim ipVal As String
Dim opValue As String
ipVal = rg.Value
For i = 1 To Len(ipVal)
- Check the character is Numeric or not
If Not IsNumeric(Mid(ipVal, i, 1)) Then
opValue = opValue & Mid(ipVal, i, 1)
End If
Next
onlyText = opValue
End Function
Step 4. Save your Excel file as “Excel Macro-Enabled Workbook” [*.xlsm]

Step 5. Type the function name “=onlyText(B5)” in cell “C5”.
Macro remove all numeric character from cell “B5” and return to cell “C5”.

Conclusion
Simply put, dealing with a mix of letters and numbers in Excel doesn’t have to be a headache. This article has some neat tricks to help you pull out only the text you want from those alphanumeric jumbles. Whether you’re a pro at spreadsheets or just getting started, these methods using Excel functions or combining them will tidy up your data, making it more sensible and user-friendly. Think of Excel like a toolbox, and by adding these text extraction skills, you’re giving yourself an edge in handling all kinds of alphanumeric puzzles. So, give it a shot, try out these techniques, and watch how they can make your Excel adventures smoother and more productive!
Similar Reads
How to Extract the last N Words from Text String in Excel
Excel is a tool for storing and managing a large amount of data. Data is entered in a rectangular block, which we called a cell. This tool can be used to perform different tasks like creating graphs and analyzing trends, to get insights from the data. It is primarily used for financial data analysis
3 min read
How to Extract Text before @ Sign from an Email Address in Excel?
You got a dataset and you want to extract the name part of the email address. This task can be done with a text to column and formula that uses the LEFT and FIND functions. Using LEFT and FIND functionsThe LEFT function: The LEFT function returns a given text from the left of our text string based o
3 min read
How to Extract Characters from a String in R
Strings are one of R's most commonly used data types, and manipulating them is essential in many data analysis and cleaning tasks. Extracting specific characters or substrings from a string is a crucial operation. In this article, weâll explore different methods to extract characters from a string i
4 min read
How to Extract the Last Word From a Cell in Excel
Explore how to extract the last word from a cell in Excel. Whether youâre working with text data, names, or any other content, knowing this technique can be incredibly useful. In this article, we explain how to extract the last word from a text in a cell using the Excel function. Â Extracting words f
3 min read
How to remove non-alphanumeric characters in PHP?
Non-alphanumeric characters can be remove by using preg_replace() function. This function perform regular expression search and replace. The function preg_replace() searches for string specified by pattern and replaces pattern with replacement if found. Examples: Input : !@GeeksforGeeks2018? Output
2 min read
How to Extract Unique Items From a List in Excel
Microsoft Excel is a powerful tool widely used for data manipulation and analysis. Extracting unique items from a list is a common task in Excel that involves identifying and removing duplicates, leading to cleaner and more accurate data. We often need to report Unique customers/products/items from
6 min read
How to extract Numbers From a String in PHP ?
Extracting numbers from a string involves identifying and isolating numerical values embedded within a text. This process can be done using programming techniques, such as regular expressions, to filter out and retrieve only the digits from the string, ignoring all other characters. Here we have som
3 min read
How to Read Data From Text File in Excel VBA
VBA Program to read a Text file line by line (Sales Data) and place it on a worksheet It provides a concise introduction to the process of importing data from text files into Microsoft Excel using Visual Basic for Applications (VBA). This introductory article serves as a starting point for individua
5 min read
Shell Script To Check That Input Only Contains Alphanumeric Characters
If you want an input which contains only alphanumeric characters i.e. 1-9 or a-z lower case as well as upper case characters, We can make use of regular expression or Regex in short in a Shell Script to verify the input. Example: Input: Geeksforgeeks Output: True Explanation: Here all the inputted d
2 min read
How to Find the Longest or Shortest Text String in a Column in Excel?
In this article, we will see how to find the longest or shortest text string in a column in Excel? Usually, for finding the longest or shortest string we can visit the all string in columns one by one and compare them to get results. This seems to work when you have less amount of data in an excel s
4 min read