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)


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.


=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.


Thank you! This works too