How to keep leading zeroes in Excel 2016 text cell after replace (Ctrl H)-CodePudding

I have a few hundred cells with values like 0100234X. The cells are Text format, but after Ctrl H, replace X with blanks to remove it, Excel 2016 remove leading zeroes as well. The cells are still Text format.
Any idea how to force it to keep zeroes after replace?
I have a workaround - to remove X in Notepad and then paste values in Excel - then leading zeroes stay.
I hope you can tell me how to make it directly.
PS. If i delete X with backspace in a single cell, then Excel keep leading zeroes.

CodePudding user response：

You could do this with helper column (if your cells are in one column).

=SUBSTITUTE(A1,"X","")

=REPLACE(A1,FIND("X",A1),1,"")

will both give wanted result: