Remove all numbers before a text

Formula to Remove all numbers before a text in Excel

Learn how to remove all numbers before a text in Excel using the TEXTJOIN, MID, TRUE, IF, ISNUMBER, LEN, and INDIRECT functions.

In Excel, sometimes you need to extract specific data, such as numbers or text, from a mixed string. For example, you might want to extract the part of a string that comes after the numbers or letters. The “remove all numbers before a text” approach helps clean up data where the string contains a mix of alphanumeric characters.

How to delete all numbers before a text

To remove all numbers before a text in Excel, follow these steps:

  1. Open Excel.
  2. Type =TEXTJOIN(“”, TRUE, IF(ISNUMBER(MID(B3, ROW(INDIRECT(“1:” & LEN(B3))), 1) * 1), “”, MID(B3, ROW(INDIRECT(“1:” & LEN(B3))), 1))).
  3. Press Enter.
  4. The formula will remove all numbers before the given text.

Explanation

This formula is advanced, but we can break it down into smaller parts for better understanding:

Step 1.: MID(B3, ROW(INDIRECT(“1:” & LEN(B3))), 1):

  • LEN(B3): This calculates the string length in cell B3.
  • INDIRECT(“1:” & LEN(B3)): This generates an array of numbers from 1 to the length of the string in B3.
  • ROW(INDIRECT(“1:” & LEN(B3))): Creates a row-wise array {1, 2, 3, …, n} corresponding to each character position in the string.
  • MID(B3, ROW(…), 1): Extracts one character at a time from B3 for each position of the string (from the first character to the last).

Step 2.: ISNUMBER(… * 1):

  • MID(B3, ROW(…), 1) returns each character in the string individually.
  • Multiplying the character by one will return a number if it is numeric and an error if it is non-numeric (e.g., a letter or symbol).
  • ISNUMBER(… * 1) checks if the character is a number. If it is, it returns TRUE; otherwise, it returns FALSE.

Step 3.: IF(ISNUMBER(…), “”, MID(…)):

  • If the character is a number (ISNUMBER is TRUE), it replaces the number with an empty string (“”).
  • If the character is not a number, it retains the character using MID(B3, …).

Step 4.: TEXTJOIN(“”, TRUE, …):

  • TEXTJOIN concatenates the results into a single string, joining all characters that were not numbers.
  • The TRUE argument ensures that empty values are ignored, so the numbers replaced with “” are not included in the final result.

Result:

remove all characters before number

Okay, let’s take a look at another method.

Download the practice file!

Conclusion

In summary, the formula in the example efficiently removes all leading numbers from a string and extracts only the text portion.