converting text to numeric data
 

I have data that I am copying and pasting from a web site that I have to
import as text and then manually insert column widths so that it will not all
be placed in one column but into separate columns. My problem comes in
because I cannot now use the data in a formula. It will not compute because
it is in text format. I have tried the paste special multiply trick but
that doesn't work because there is a space prior to the number and that is
throwing off the equation. can anyone help me? Do I have to import as text?
If I do a copy & paste then the info goes into the cell A1 only and notthing
into the other cells.

--
trying to work this out

converting text to numeric data
 

Hi!

Here are some things to try:

Select the range of cells that contain the numbers.
Goto DataText to Columns
Select Fixed width
Click Next, Next, Finish

If the "spaces" were standard char 32 spaces this should remove them and
convert the values to numbers.

If the "spaces" are char 160 (html nbsp;) T to C won't work.

Select the range of numbers
Goto EditReplace
Find what: ALT 0160 (hold down the ALT key and using the numeric keypad type
0160)
Replace with: nothing, leave this empty
Replace All

Biff

"babooz" wrote in message
...
I have data that I am copying and pasting from a web site that I have to
import as text and then manually insert column widths so that it will not
all
be placed in one column but into separate columns. My problem comes in
because I cannot now use the data in a formula. It will not compute
because
it is in text format. I have tried the paste special multiply trick but
that doesn't work because there is a space prior to the number and that is
throwing off the equation. can anyone help me? Do I have to import as
text?
If I do a copy & paste then the info goes into the cell A1 only and
notthing
into the other cells.

--
trying to work this out

converting text to numeric data
 

P.S.

If you copy/paste from the web a lot (I do) I would highly recommend you use
this macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Attach it to a button and put it on one of your toolbars. This macro strips
out all extraneous char 32 spaces and char 160 nbsp spaces.

Biff

"Biff" wrote in message
...
Hi!

Here are some things to try:

Select the range of cells that contain the numbers.
Goto DataText to Columns
Select Fixed width
Click Next, Next, Finish

If the "spaces" were standard char 32 spaces this should remove them and
convert the values to numbers.

If the "spaces" are char 160 (html nbsp;) T to C won't work.

Select the range of numbers
Goto EditReplace
Find what: ALT 0160 (hold down the ALT key and using the numeric keypad
type 0160)
Replace with: nothing, leave this empty
Replace All

Biff

"babooz" wrote in message
...
I have data that I am copying and pasting from a web site that I have to
import as text and then manually insert column widths so that it will not
all
be placed in one column but into separate columns. My problem comes in
because I cannot now use the data in a formula. It will not compute
because
it is in text format. I have tried the paste special multiply trick
but
that doesn't work because there is a space prior to the number and that
is
throwing off the equation. can anyone help me? Do I have to import as
text?
If I do a copy & paste then the info goes into the cell A1 only and
notthing
into the other cells.

--
trying to work this out

converting text to numeric data
 

Thanks Biff you guys are so helpful.
--
trying to work this out


"Biff" wrote:

P.S.

If you copy/paste from the web a lot (I do) I would highly recommend you use
this macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Attach it to a button and put it on one of your toolbars. This macro strips
out all extraneous char 32 spaces and char 160 nbsp spaces.

Biff

"Biff" wrote in message
...
Hi!

Here are some things to try:

Select the range of cells that contain the numbers.
Goto DataText to Columns
Select Fixed width
Click Next, Next, Finish

If the "spaces" were standard char 32 spaces this should remove them and
convert the values to numbers.

If the "spaces" are char 160 (html nbsp;) T to C won't work.

Select the range of numbers
Goto EditReplace
Find what: ALT 0160 (hold down the ALT key and using the numeric keypad
type 0160)
Replace with: nothing, leave this empty
Replace All

Biff

"babooz" wrote in message
...
I have data that I am copying and pasting from a web site that I have to
import as text and then manually insert column widths so that it will not
all
be placed in one column but into separate columns. My problem comes in
because I cannot now use the data in a formula. It will not compute
because
it is in text format. I have tried the paste special multiply trick
but
that doesn't work because there is a space prior to the number and that
is
throwing off the equation. can anyone help me? Do I have to import as
text?
If I do a copy & paste then the info goes into the cell A1 only and
notthing
into the other cells.

--
trying to work this out