Search code examples
excelreplaceexcel-2013

Replacing all letters in Excel

I have cells with values like 01EL041 in Excel and I would like to replace all letters with a fixed character (in my case, 0, leaving 01041 in this example). Is there a good way to do this without VBScript? I know I could do 26 SUBSTUITUTEs but that seems terrible.

Solution

  • If you have Office 365 Excel then use this array formula:

    =CONCAT(IFERROR(--MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0))

    Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here

    for prior versions that do not have CONCAT you can use this array formula:

    =TEXT(SUM(IFERROR(--MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0)*10^(LEN(A1)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))),REPT("0",LEN(A1)))

    Again, being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here