Forum Discussion

Ansoon Al Busaidi's avatar
Ansoon Al Busaidi
Copper Contributor
Mar 01, 2018

Replacing Cells with different extension type in Excel with blank

hello, Am looking for a way that i can replace cells with different extensions ( .pdf, .doc ,.xls ,.tiff ...ect) in a sheet in Excel with blank , how can i do that?

thank you

excel
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Mar 01, 2018

    Hi Ansoon,

     

    You can either use Find and Replace tool or REPLACE function.

     

    With Find and Replace, press Ctrl+H, type the extension with its dot (.xlsx) in Find what box, leave the Replace with box empty, and then hit Replace All.

    But do that for each extension that you have.

     

    With REPLACE function, you can use the below formula, and you will find it in the attached file.

    =REPLACE(A1,SEARCH(".",A1),LEN(A1)-SEARCH(".",A1)+1,"")

    This formula can replace all extensions at once, But works only if there are no dots (.) in the file extension except the dot of the extension like this: File.doc

     

Resources