Manual Reformatting

  • Thread starter Thread starter Joseph Sichelstiel
  • Start date Start date
J

Joseph Sichelstiel

I manipulate alot of data in order to make databases
(such as getting the left 5 characters of a column).
Whenever I make databases using formulas (and then copy +
paste special + paste values) it always formats the data
into text.

I usually need this data to be in number format, and the
only way to format this is to format the entire column
as "numbers," and then go into every single cell with
data and "manually" reset the formatting (I need to press
f2 to bring up the cursor, then press enter). This is
the only way to format this kind of text into numbers.

What is going on? Why can't I format these cells
normally, why do I need to go into every single one
and "manually" reset them?
 
You don't - Copy an empty cell, select all your data and do Edit / Paste Special / Add. thsi will
coerce it all back to numeric.

You could though, simply change your formulas to make the data numeric to start with, eg:-

=LEFT(A1,5) will give you a textual answer, whilst:-

=--LEFT(A1,5) will give you a numeric answer to start with, saving you having to do what I said
above at all.

Also, if you are just looking for the leftmost 5 characters of every cell, and those cells are all
the same length to start with, then you could look at Data / Text To Columns / Fixed Width, set
the break at the 5th character, and then format the column you want to import as numeric in the
wizard. That way you don't have to any of what I said above.
 
Back
Top