Identify cells that end in 5 numbers

[shivus]

Hi - I have a column which has names of businesses but some of them have the zip code attached to them. For example most cells are just
ABC Company
123 Company
Def Company

But some are
ABC Company90210
123 Company10011
Def Company20007

In all the cells that have zip codes, they are always 5 digits (o 90210 and not 90210-1107) and they always start right after the last letter of the Business Name (there is no space)

Is there a way I can identify just the cells that have the zip code attached to the business name and remove the zip code digits? I do not need to save them to a different column. I just need to clean the Business Name column so those numbers dont appear next to the business name in certain cells.

Thanks!!!

[Imran Laldin]

Dear,

Please look in to the attach file, maybe it will solve your problem.

[Glenn Kennedy]

Try this and copy down

=IF(ISNUMBER(RIGHT(A1,5)+0),LEFT(A1,LEN(A1)-5),A1)

[Kaper]

easy way would be to write a formula in a cell next to such name (say it's in A1, so in B1):

=IF(ISERROR(VALUE(RIGHT(A1,5))),A1,LEFT(A1,LEN(A1)-5))

copy this formula down (double click on filling handle - see https://support.office.com/en-za/art...2-35a236c5b5db )copy whole column and paste it special (as values) into A
then delete no longer needed formulas

[shivus]

Thank you so much Kaper and Glenn - That worked great!!!!

[Glenn Kennedy]

You're welcome...

[Kaper]

And thanks for marking thread as Solved.

It would be lovely if you colud also "add some reputation" to those who helped by clicking a small star icon in lowerleft corner of our post(s).
As opposite to many other sites, you can grant reputation to several posts and not only in threads started by you.

Best Regards,