You can use the following formula to extract numbers from a string in Excel:
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
This particular formula will extract all numbers from the string in cell A2.
For example, if cell A2 contains the phrase “25 bikes” then this formula will simply return 25.
The following example shows how to use this formula in practice.
Example: Extract Numbers from String in Excel
Suppose we have the following list of strings in Excel:
Suppose we would like to extract only the numbers from each string.
We can type the following formula into cell B2 to do so:
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
We can then click and drag the formula down to each remaining cell in column B:
Column B now contains only the numbers from each of the corresponding strings in column A.
How This Formula Works
Recall the formula that we used to extract the numbers from the strings:
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Here is how this formula works:
First, ROW(INDIRECT(“1:”&LEN(A2))) returns a series of numbers from 1 to the length of the string.
Next, (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) converts all text characters to #VALUE! errors.
Next, IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) removes all $VALUE! errors.
Lastly, TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) joins together all the numbers that remain and ignores any blanks.
The end result is that we’re able to extract only the numbers from each string.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Extract Text Between Two Characters
Excel: How to Extract Text After a Character
Excel: How to Extract Text Before a Character
How do I extract everything after the first letter. The first letter could be the 1st character, the 5th, or the 30th. For example,
STRING DESIRED RESULT
123abdefg abdefg
1 2 3 & – .? 1– WHYWHY WHYWHY
648?! ?!{- + XU1XU#1!!:) XU1XU#1!!:)
To extract **everything after the first letter** (ignoring numbers, spaces, and symbols before that), you can use a **formula in Excel** with a **combination of functions** like `MID`, `FIND`, and `LET` (if available), or an **array formula** using `TEXTJOIN` and `MID`.
Here’s a robust way to do it using Excel’s **LAMBDA** (Office 365 / Excel 2021+):
—
### ✅ **Formula (Office 365 / Excel 2021+):**
“`excel
=LET(
txt, A1,
pos, MIN(FILTER(SEQUENCE(LEN(txt)),ISNUMBER(FIND(MID(txt,SEQUENCE(LEN(txt)),1),”ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz”)))),
MID(txt,pos,LEN(txt))
)
“`
**Explanation:**
– `SEQUENCE(LEN(txt))` creates a list of all character positions in the string.
– `MID(…,1)` checks each character.
– `FIND(…,”A-Z”)` locates positions of alphabet characters.
– `MIN(FILTER(…))` finds the position of the **first letter**.
– `MID(txt,pos,LEN(txt))` extracts everything from the **first letter to the end**.
—
### ⚠️ If you’re using an older version of Excel (2016 or earlier), here’s a **user-defined function** using **VBA**:
#### 1. Press `ALT + F11` → Insert → Module → Paste:
“`vba
Function ExtractAfterFirstLetter(txt As String) As String
Dim i As Long
For i = 1 To Len(txt)
If Mid(txt, i, 1) Like “[A-Za-z]” Then
ExtractAfterFirstLetter = Mid(txt, i)
Exit Function
End If
Next i
ExtractAfterFirstLetter = “”
End Function
“`
#### 2. Use in Excel like:
“`excel
=ExtractAfterFirstLetter(A1)
“`
—
### 🧪 Examples:
| Original String | Extracted Result |
|—————————————–|———————–|
| `123abdefg` | `abdefg` |
| `1 2 3 & – .? 1– WHYWHY` | `WHYWHY` |
| `648?! ?!{- + XU1XU#1!!:)` | `XU1XU#1!!:)` |