Removing last character of CELL if it's a (number / space / comma)

[Accorda]

Dear Excel community,

I'm facing a problem which I can't solve after trying a lot of different options. I attached an excel file as an example.

Following is the problem:

I'm using a column with in each cell a product description including some article numbers. I now want to remove all these article numbers since they're outdated.

For example:

This is a product description, for product A 4353434323, 42354252435, 324325432, 56003324, 3445435

This is a product description, for product B 723487234, 443563214

I need to have these article numbers removed, the problem is, some descriptions have 2 article numbers, some 4 or some even more than 10.

So my logic would be something like: Remove last character if it's equal to a number / space / comma. In that way it would strip out everything except the description itself. I thought in this direction: =IF(RIGHT(A1,1)=" ", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ",",LEFT(A1,LEN(A1)-1),A1) but that obviously doesn't work.

In the example file is some test data (5 in total) in reality we're talking about more than 2000+ descriptions. So deleting by hand wasn't my first choice as you probably can imagine.

I'm probably overthinking once again. Could you help me?

Many thanks in advance.

Accorda

[AliGW]

Welcome to the forum.

Based on your sample, try this:

=LEFT(A1,FIND(CHAR(10),A1)-1)

or this for your locale:

=LEFT(A1;FIND(CHAR(10);A1)-1)

It uses the carriage return as the separator.

[Accorda]

Dear AliGW,

That seems to work perfectly! I would have never thought that it could be solved with such a short formula.

Many thanks!!

Have a great day, thread can be set to SOLVED.

Kind regards,

Accorda

[bebo021999]

In case, there are more than 1 space (char (10)) in A1, and no number within description:

Please Login or Register  to view this content.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.