Skip to content
TheChatArchiveThe Chat Archive
Metallama-4-maverick-17b-128e-instruct· MetaHugging Face LMArena Human Preference 140K

In Excel, take everything before the first number in a cell

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen3-235b-a22b’s answer.
  1. User

    In Excel, take everything before the first number in a cell

  2. Meta Assistant

    To extract everything before the first number in a cell in Excel, you can use a combination of the MIN, FIND, and LEFT functions. 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:

    1. 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 cell A1. It returns an array of positions for each digit found.
    2. IFERROR(..., "") handles cases where a digit is not found, replacing the error with an empty string.
    3. MIN(...) finds the smallest position (i.e., the first number encountered).
    4. LEFT(A1, ...) extracts all characters from the start of the string up to (but not including) the first number.

    Example:

    If A1 contains:
    Hello123World

    The formula will return:
    Hello

    Notes:

    • 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 MINIFS or XLOOKUP functions, but the array-based FIND approach works in most Excel versions.

    Let me know if you need further adjustments!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.