CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5471

Extract numbers before any letter or special sign within cell content

I have the following Excel spreadsheet:

         A                  B              C
1   125Product             125
2   58968Brand           58968
3   74Category              74
4   6325Product           6325
5   2:2                     2:
6   7489Category458    7489Bra
7   29:Brand                29
8

In Column A I have contents consisting of numbers, letters or special characters.
In Column B I want to extract all numbers before any letter or special character; therefore, I use the following formula:

=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

This formula works for all values except for the values in Cell A5 and A6. I guess the issue is that in this cell - instead of all the other values - there is a another number after the letter/special character.

What do I need to change in my formula to only extract the numbers before any letter or special character?

Upvotes: 1

Views: 2710

Answers (1)

JvdV
JvdV

Reputation: 75840

enter image description here

Formula used in B1:

=LEFT(A1,MATCH(FALSE,ISNUMBER(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)*1),0)-1)

Confirm through Ctrl+Shift+Enter

If you don't want to enter as array, use:

=LEFT(A1,MATCH(FALSE,INDEX(ISNUMBER(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)*1),0),0)-1)

Upvotes: 2

Related Questions