Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers
Here's what I have: Spreadsheet with column of currency that was formatted
as "text". I inserted a column to the right - put in formula =" "&A1 and the numbers then appears as numbers (without the little green tab in the corner). Then I wanted to delete the first column but can't because formula refers to it. I inserted a third column, copied 2nd column and paste special (values) but the text tab reappears. Is there some way to eliminate the original text column or copy the second column and keep it formatted as just number or currency? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers
I enter '1/1/2007 in A1; the ' makes this text
In B1 I entered =--F1; the double negation coheres the text to number I am working in dd/mm/yyyy format so the number I get is 39083 When I format this as date it show the correct date Now I can select B1 (or a whole range( use Copy followed by Paste Special Value. Please note: you do not need to do this in a new column, you can paste over the top of the formulas The same method worked when I preformatted a cell as text and entered a date I could not get you " "&A1 method to work best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Cheri" wrote in message ... Here's what I have: Spreadsheet with column of currency that was formatted as "text". I inserted a column to the right - put in formula =" "&A1 and the numbers then appears as numbers (without the little green tab in the corner). Then I wanted to delete the first column but can't because formula refers to it. I inserted a third column, copied 2nd column and paste special (values) but the text tab reappears. Is there some way to eliminate the original text column or copy the second column and keep it formatted as just number or currency? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers
"Cheri" wrote in message
... Here's what I have: Spreadsheet with column of currency that was formatted as "text". I inserted a column to the right - put in formula =" "&A1 and the numbers then appears as numbers (without the little green tab in the corner). Then I wanted to delete the first column but can't because formula refers to it. I inserted a third column, copied 2nd column and paste special (values) but the text tab reappears. Is there some way to eliminate the original text column or copy the second column and keep it formatted as just number or currency? I don't think the little green tabs have anything to do with it being text or not. The formula =" "&A1 will not convert text to a number. Its result is definitely text. To convert text to a number use =VALUE(A1). Copy and paste special (values) on the results of this and you should achieve what you want. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers
Thanks Bernard but I wasn't converting dates. I was trying to convert a
column of numbers formatted as text into just plain numbers or currency. I figured it out by using =Value(A1) then copy, paste special value. Thanks though "Bernard Liengme" wrote: I enter '1/1/2007 in A1; the ' makes this text In B1 I entered =--F1; the double negation coheres the text to number I am working in dd/mm/yyyy format so the number I get is 39083 When I format this as date it show the correct date Now I can select B1 (or a whole range( use Copy followed by Paste Special Value. Please note: you do not need to do this in a new column, you can paste over the top of the formulas The same method worked when I preformatted a cell as text and entered a date I could not get you " "&A1 method to work best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Cheri" wrote in message ... Here's what I have: Spreadsheet with column of currency that was formatted as "text". I inserted a column to the right - put in formula =" "&A1 and the numbers then appears as numbers (without the little green tab in the corner). Then I wanted to delete the first column but can't because formula refers to it. I inserted a third column, copied 2nd column and paste special (values) but the text tab reappears. Is there some way to eliminate the original text column or copy the second column and keep it formatted as just number or currency? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers
To convert "Text Numbers" to numbers is easier than that.
Type a number one in a spare blank cell and then copy the cell. Highlight the text numbers that need to be converted, right click, paste special, select multiply in the Operation area. Click ok out of it. The text numbers are now numerical numbers. Enjoy -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- "Cheri" wrote in message ... Here's what I have: Spreadsheet with column of currency that was formatted as "text". I inserted a column to the right - put in formula =" "&A1 and the numbers then appears as numbers (without the little green tab in the corner). Then I wanted to delete the first column but can't because formula refers to it. I inserted a third column, copied 2nd column and paste special (values) but the text tab reappears. Is there some way to eliminate the original text column or copy the second column and keep it formatted as just number or currency? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |