7

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

010-038-310-1500-93745
010-038-310-3000-xxxxx-xxxxxxxx-xxxxxxxx
010-038-???-????-?????-????????
010-038-190-3109-?????-87655467-????????
010-038-310-3101-xxxxx-xxxxxxxx-xxxxxxxx
010-038-xxx-3103-xxxxx-xxxxxxxx-49359661

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

010-038-310-1500-93745
010-038-310-3000
010-038
010-038-190-3109-?????-87655467
010-038-310-3101
010-038-xxx-3103-xxxxx-xxxxxxxx-49359661

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

Improve this question

3 Answers 3

Reset to default
17
+50

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

=LEFT(A1,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))

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.

Improve this answer
4
  • 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?
    – Justin Doward
    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 !!
    – Rajesh Sinha
    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 !!
    – Rajesh Sinha
    May 9, 2020 at 10:05
3

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
MyExit:
LNum = Left(Target, i)

End Function

enter image description here

Improve this answer
2
  • First version had some extraneous code.
    – Justin Doward
    May 7, 2020 at 16:53
  • This one is working properly ,, +10 ☺
    – Rajesh Sinha
    May 9, 2020 at 10:00
1

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

=REGEXEXTRACT(A1,".*\d")

Regex captures anything but must end with a digit.

enter image description here

Can anyone with Excel please verify syntax is the same?

Improve this answer
2
  • 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.
    – Viktor Mellgren
    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 .