Log in

Excel: Remove decimal point, keep leading/trailing zeroes - fuzzygruf's blog

> Recent Entries
> Archive
> Friends
> Profile
> Homepage

LJ Update
LJ Recent Comments
Search LJ FAQs
Edit LJ userpics
Manage filters
LJ collage
Edit LJ links
LJ guests and statistics
upload to scrapbook
LJ Stats
LJ portal

September 8th, 2014

Previous Entry Share Next Entry
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).

(Leave a comment)

> Go to Top