In Excel, take everything before the first number in a cell
- User
In Excel, take everything before the first number in a cell
Assistant
To extract everything before the first number in a cell in Excel, you can use a combination of the
MIN,FIND, andLEFTfunctions. Here's a step-by-step formula to achieve this:Formula
=LEFT(A1, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")))How it works:
FIND({0,1,2,3,4,5,6,7,8,9},A1)searches for the position of the first occurrence of any digit (0 through 9) in the cellA1. It returns an array of positions for each digit found.IFERROR(..., "")handles cases where a digit is not found, replacing the error with an empty string.MIN(...)finds the smallest position (i.e., the first number encountered).LEFT(A1, ...)extracts all characters from the start of the string up to (but not including) the first number.
Example:
If
A1contains:
Hello123WorldThe formula will return:
HelloNotes:
- If the cell contains no numbers, the formula will return the entire cell content.
- If you want to handle this case differently (e.g., return an empty string), you can modify it:
=IF(MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))="", "", LEFT(A1, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))-1))This version returns an empty string if there are no numbers.
For Excel 365 or Excel 2021 and later:
You can simplify the formula using the
MINIFSorXLOOKUPfunctions, but the array-basedFINDapproach works in most Excel versions.Let me know if you need further adjustments!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.