|10:18 am, September 8th - Excel: Remove decimal point, keep leading/trailing zeroes|
A coworker came to me with a problem. He had to take hundreds of cells in Excel that had a format like 084.01 and 000923.0 and change it to 08401 and 0009230. The cells were in text format to preserve the leading zeroes. Replacing the decimal with nothing changed the fields to 8401 and 9230. (Even though these were "text"!)
One solution was to use the Text to Data button, with a delimeter of . while changing format for each column to text. Then add another column, and concatenate the two columns. This works, but for 50 columns, this would be very time-consuming.
Notepad to the rescue. Steps were simple. Copy the column to Notepad. Replace All . with blank (nothing). Copy the notepad column back to Excel (with the column formatted as text).