3

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.

Charles
  • 11,269
  • 13
  • 67
  • 105

2 Answers2

3

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Neat trick (+1)! Unfortunately I have 2013, any ideas that would work there? – Charles Nov 28 '17 at 18:48
  • O365 covers both 2013 and 2016 so it should work in 2013 without problems. – Emil Borconi Nov 28 '17 at 18:50
  • @EmilBorconi but one does need a subscription to O365 – Scott Craner Nov 28 '17 at 18:50
  • @ScottCraner I managed to make a fool out of my self twice in a period of less than 5 mins, embarrassed with myself... On a second note, that formula is some serious craft... – Emil Borconi Nov 28 '17 at 19:01
  • 1
    @ScottCraner Ingenious solution. Only thing I would change is remove volatile functions, i.e. replace `ROW(INDIRECT("1:"&LEN(A1)))` with `ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))`. – ImaginaryHuman072889 Nov 28 '17 at 19:05
  • @ImaginaryHuman072889 habits are hard to break, that is good, never thought of that, and I should have. – Scott Craner Nov 28 '17 at 19:08
  • 1
    @ScottCraner The irony is that you have pointed out to me in many other posts to remove volatile functions when I used `INDIRECT`. Just returning the favor now :P – ImaginaryHuman072889 Nov 28 '17 at 19:09
  • 1
    @ImaginaryHuman072889 I get blinded by habit. when I wanted to iterate numbers I always used some form of ROW(INDIRECT()) it is what I learned and never questioned after learning to shun the volatility of INDIRECT. I never separated the two functions till now. Even us old dogs can learn a new trick here and there. Thank you. – Scott Craner Nov 28 '17 at 19:13
-1

No need of VB.

Simple, press Ctrl+H (fro replace) in the search write ? and replace it with whatever you want.

EDIT

If by all letters you mean any character than this is the easiest way, if not please refer to Scott's answer.

Emil Borconi
  • 3,326
  • 2
  • 24
  • 40