Changing from Numeric to Text

  • Thread starter Thread starter Ben W
  • Start date Start date
B

Ben W

Hi,

I copy & paste a part number from notepad into a sheet, e.g 7000000002009
and appears as 7E+12.. 2

When I change the whole column to text, the 7E+12 remains and is not
converted back to the full value, unless I actually click in the cell edit
area at the top of the sheet (sorry don't know exactly what that is called!)

How can I set these all to appear correctly?

Thanks
 
-----Original Message-----
Hi,

I copy & paste a part number from notepad into a sheet, e.g 7000000002009
and appears as 7E+12.. 2

When I change the whole column to text, the 7E+12 remains and is not
converted back to the full value, unless I actually click in the cell edit
area at the top of the sheet (sorry don't know exactly what that is called!)

How can I set these all to appear correctly?

Thanks



.
Click to expand...
 
Ben W said:
Hi,

I copy & paste a part number from notepad into a sheet, e.g 7000000002009
and appears as 7E+12.. 2

When I change the whole column to text, the 7E+12 remains and is not
converted back to the full value, unless I actually click in the cell edit
area at the top of the sheet (sorry don't know exactly what that is called!)

How can I set these all to appear correctly?

Thanks
Click to expand...

Don't format as text unless there's a specific reason. Format as a number
(not general).
Note that in the Format Cells dialog box there is a "Sample" area (just
beneath the tabs). If you have a number in a cell and go into this dialog
box to format that cell, as you select different formats the "Sample" area
shows how the number will appear.
 
I have had the same problem many times. What follows is
a work-a-round that I developed. There may well be other
more efficient methods that I would like to also learn.

Insert a (General-format) column next to the column of
data about which you question. i.e. (B1)

In new column (A1) Enter the formula =Clean(Trim(B1)).
Copy the formula down.

After Column A is filled, Copy Column A and Paste
Special> Values back to column A. Now Column A will
stand on its own.

NOTE: if you enter a cell in column A with your cursor it
will revert to the Scientific Notation.

I can not tell you why all this needs to be done - but it
works for me.

The above, is what I do for assuring success with VLOOKUP
() issues.

If you get any other good info please email:
(e-mail address removed)

Good Luck & Happy Holidays
 
Thanks for that.

Unfortunately I also have figures that have leading zero's, which I need to
keep.

eg. 0020003388, gets formatted as 20003388. I need the leading zeros.
Similarly to the other reply from Dennis, I am using this as a VLOOKUP and
trumming the leading zeros is causing the match to fail...?

If I format to text BEFORE I paste the data in, then it works OK. But there
are times when I need to format the values AFTER.? Can this be done without
too many workarounds?
 
There are two fundamentally different formats in Excel: text and number.
(The other formats, such as currency, percentage, date, time, etc., are
really just different ways of displaying numbers.) The distinction between
text and number is very important, as functions such as VLOOKUP will not
work if you try to look up a text value amongst numbers or vice-versa.

A cell entry is automatically text if it contains any character other than
the digits 0 to 9 and the decimal separator ("." or "," depending on your
regional settings). Two sources of confusion enter the picture here:

1. Dates may be DISPLAYED with "/" or "-" separators, or the month in
letters; times are DISPLAYED with ":" separators; percentages are DISPLAYED
with the "%" symbol; currency is DISPLAYED with a currency symbol; numbers
may be DISPLAYED with the thousand separator "," and so on. The point is
that these characters are part of the way the number is displayed, NOT part
of the underlying value. That's why such entries can easily be reformatted
to display differently.
Type the number 37972.5 into a cell formatted as general and that's what you
see.
Format this cell as date (mm/dd/yy) and you will see 12/17/03.
Format it as time (hh:mm) and you will see 12:00.
Format as European date and am/pm time and you will see 17/12/03 12:00 PM.
Format as percent and you will see 3797250%.
These are all just different ways of DISPLAYING the same underlying value of
37972.5.

2. A number can be forced to become text, for example by formatting the cell
as text BEFORE entering the data (but not after), or preceding what is typed
in with an apostrophe. It will not look any different (except being
left-aligned rather than right-aligned in a general format cell). But it
will react as text in functions.

Now, there are two ways of getting a 'number' to display leading zeros.
The first is to format as a number that always has a certain number of
digits, e.g. 0000000000 for 10 digits. Then, if you type in 20003388 you
will see 0020003388. This is a number and so will respond in functions as a
number. Apart from how it is displayed, it is indistinguishable from
20003388.
The second way is to change it to text. Then you have to type in the leading
zeros for them to appear, and "0020003388" is quite different from
"20003388".

Clearly, therefore, you must make the same choice between these two ways for
"lookup value" and "table array"
if VLOOKUP is to work. My advice is to use numbers if you possibly can,
formatting with leading zeros if required. Use text only where necessary.
However, the choice is yours, but you must choose one and stick to it - you
cannot mix text and numbers and expect things to still work.
 
That was an excellent explanation!
-----Original Message-----
There are two fundamentally different formats in Excel: text and number.
(The other formats, such as currency, percentage, date, time, etc., are
really just different ways of displaying numbers.) The distinction between
text and number is very important, as functions such as VLOOKUP will not
work if you try to look up a text value amongst numbers or vice-versa.

A cell entry is automatically text if it contains any character other than
the digits 0 to 9 and the decimal separator ("." or "," depending on your
regional settings). Two sources of confusion enter the picture here:

1. Dates may be DISPLAYED with "/" or "-" separators, or the month in
letters; times are DISPLAYED with ":" separators; percentages are DISPLAYED
with the "%" symbol; currency is DISPLAYED with a currency symbol; numbers
may be DISPLAYED with the thousand separator "," and so on. The point is
that these characters are part of the way the number is displayed, NOT part
of the underlying value. That's why such entries can easily be reformatted
to display differently.
Type the number 37972.5 into a cell formatted as general and that's what you
see.
Format this cell as date (mm/dd/yy) and you will see 12/17/03.
Format it as time (hh:mm) and you will see 12:00.
Format as European date and am/pm time and you will see 17/12/03 12:00 PM.
Format as percent and you will see 3797250%.
These are all just different ways of DISPLAYING the same underlying value of
37972.5.

2. A number can be forced to become text, for example by formatting the cell
as text BEFORE entering the data (but not after), or preceding what is typed
in with an apostrophe. It will not look any different (except being
left-aligned rather than right-aligned in a general format cell). But it
will react as text in functions.

Now, there are two ways of getting a 'number' to display leading zeros.
The first is to format as a number that always has a certain number of
digits, e.g. 0000000000 for 10 digits. Then, if you type in 20003388 you
will see 0020003388. This is a number and so will respond in functions as a
number. Apart from how it is displayed, it is indistinguishable from
20003388.
The second way is to change it to text. Then you have to type in the leading
zeros for them to appear, and "0020003388" is quite different from
"20003388".

Clearly, therefore, you must make the same choice between these two ways for
"lookup value" and "table array"
if VLOOKUP is to work. My advice is to use numbers if you possibly can,
formatting with leading zeros if required. Use text only where necessary.
However, the choice is yours, but you must choose one and stick to it - you
cannot mix text and numbers and expect things to still work.

zero's, which I need
to it works OK. But
there this be done
without reason. Format as a
number and go into this
dialog formats the "Sample"
area


.
Click to expand...
 
I have that quite often when I import various part numbers an PO number
and I have found that if you change the column to TEXT then you will b
able to maintain your data as what it is.

You will loose the ability to sort it as numbers. It wil sort as alph
numeric text....

Hope this help
 
Back
Top