Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
Search/Extract Data w/in Text File | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |