How to keep leading zeroes in Excel 2016 text cell after replace (Ctrl+H)
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.
Solution
You could do this with helper column (if your cells are in one column).
=SUBSTITUTE(A1,"X","")
or
=REPLACE(A1,FIND("X",A1),1,"")
will both give wanted result:
- Convert Range to a table
- In Excel, I wonder that how to use VLOOKUP function in OR logic
- Simple VBA array join not working
- Excel Pivot Table - Turn two columns with same values into a matrix pivot table with column names as rows and value count in columns
- Excel: Transforming automatically a cell based on the input in another one
- Check Duplicate Data
- Finding hidden or system files containing ~ using Dir() in VBA
- Deleting rows that do not have specific value (text) - throwing Error Code 13
- Filter function Delivers the Blank cells in the Array as Zeros and Other Blank cells in time formated cells as 12:00
- How to disable Excel's automatic cell reference change after copy/paste?
- Retrieve article name and store of cheapest article for a given category in Excel
- Filter function or alternative for Vertical and Horizontal Criteria
- Calculate SUMPRODUCT with multiple column criteria and one row criteria
- Format smallest value in range of same category in Excel
- Calculate SUMPRODUCT with multiple column criteria and one row criteria (with empty columns in the data range)
- How to group an array of shapes
- Extract text after last delimiter but exclude last character
- How do i calculate difference between counts for each value in Excel
- Highlight cell in a range if the cell value is same as the cell value in a column A
- How to count specific values between other values
- Excel Chart: color data labels by categories - rows
- Get table dynamically with name in excel
- Excel Chart: color data labels by categories - columns
- VBA Error 1004 occurs when manipulating a table
- Store location of cell and loop recursively
- Excel - 3D interpolation
- Excel Data Validation with XLOOKUP
- Using a list (range) of column critiera (not single cells) in FILTER formula
- Yield to maturity / IRR in Power Query M
- How to convert a worksheet to a Data frame in Pandas?