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

• Initialize variable

ipVal = rg.Value

• Iterate the input string

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

• Return output

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!