I have text in Excel that is a combination of numbers, letters, and symbols.


I would like to remove all characters in the text that come after the last instance of a number:


Is there a way to do this with a formula in Excel?

3 Answers 3


For data in A1, in another cell, enter the array formula:


enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

  • That is a great formula, took me a while to peel it apart but I am not used to using array formulae. What confuses me now is that it appears to work in my excel 365 just fine without being entered as an array formula, I guess the array is just contained within the max function? May 7, 2020 at 5:13
  • @JustinDoward For what it's worth, it does not seem to work for me as a non-array formula in Excel 2016.
    – User1974
    May 7, 2020 at 12:43
  • Hi @Gary's Student ,, unfortunately it's not working for me, returns 01 from all cells ,, Check this link !! May 9, 2020 at 9:39
  • Hi @Gary's Student,, I've fixed the issue it should be an array (CSE) formula rather than shown above !! May 9, 2020 at 10:05

A UDF to do the same:

Function LNum(Target As Range) As String

Dim i As Long

For i = Target.Characters.Count To 1 Step -1
If IsNumeric(Target.Characters(i, 1).Text) Then GoTo MyExit
Next i
LNum = Left(Target, i)

End Function

enter image description here

  • First version had some extraneous code. May 7, 2020 at 16:53
  • This one is working properly ,, +10 ☺ May 9, 2020 at 10:00

I don't have excel installed, but same syntax seem to apply as in google sheets. Very simple regex should be easy to understand.


Regex captures anything but must end with a digit.

enter image description here

Can anyone with Excel please verify syntax is the same?

  • 3
    The syntax isn't the same, there's no built in regex spreadsheet functions in Excel. That said, it's very easy to whip up a custom function in VBA using the Microsoft VBScript Regular Expressions library.
    – Josh Eller
    May 7, 2020 at 14:57
  • Looked some wrong infromation then. Feels like regex is the way to go when you read the problem description. However I can see that scripting in VBA might be a too high of a threshold for some. May 11, 2020 at 7:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .