fuzzygruf: (smiley)
[personal profile] fuzzygruf

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).

Profile

fuzzygruf: (Default)
fuzzygruf

April 2017

S M T W T F S
      1
2345678
9 101112131415
16171819202122
23242526272829
30      

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jul. 23rd, 2017 08:34 am
Powered by Dreamwidth Studios