Replacing all letters in Excel

Question

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.

Answer 1

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.

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.