SOLVED

Delete group of unique digits throughout cells

New Contributor

I have addresses that I need in column C. At the end of each address is 7-8 digits grouped together that are unnecessary. I would like to delete those digits but leave the address. The problem is, there's 10,000+ cells like this. Is there a shortcut or formula to accomplish this?

 

Here's an example:

123 Main st. 12345678

3 Replies
best response confirmed by UriahWDI (New Contributor)
Solution

@UriahWDI 

Try flash fill.

Enter the first address without the unwanted digits in the cell next to it.

With that cell selected, click Fill > Flash Fill in the Editing group of the Home tab of the ribbon.

@UriahWDI 

=IF(MID(B2,LEN(B2)-8,1)=" ",LEFT(B2,LEN(B2)-9),IF(MID(B2,LEN(B2)-7,1)=" ",LEFT(B2,LEN(B2)-8),""))

You can try this formula which returns the expected result in my sheet.

address.JPG 

Thank you! This works too