Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert numbers stored as text to numbers
Help!! i'm going mad
how do you Convert numbers stored as text to numbers useing a formula (normaly comes up with and error..... but not today) Thanking you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert numbers stored as text to numbers
Hi,
Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. cheers, Shane Devenshire "Stig - tame racing driver" wrote: Help!! i'm going mad how do you Convert numbers stored as text to numbers useing a formula (normaly comes up with and error..... but not today) Thanking you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert numbers stored as text to numbers
A number of options worth looking at:
Data/ Text to Columns Copy a zero (or an empty cell) and then Edit/ Paste Special/ Add to your range of text numbers (or similarly Edit/ Paste Special/Multiply by 1) -- David Biddulph "Stig - tame racing driver" wrote in message ... Help!! i'm going mad how do you Convert numbers stored as text to numbers useing a formula (normaly comes up with and error..... but not today) Thanking you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert numbers stored as text to numbers
Still not working ........ never mind
"Shane Devenshire" wrote in message ... Hi, Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. cheers, Shane Devenshire "Stig - tame racing driver" wrote: Help!! i'm going mad how do you Convert numbers stored as text to numbers useing a formula (normaly comes up with and error..... but not today) Thanking you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert numbers stored as text to numbers
In addition to the methods given, you can use this in a helper column:
=VALUE(A1) or =A1 * 1 then copy down, assuming your text number is in A1. You can fix the values and then paste over the originals from the helper column. Hope this helps. Pete On Nov 13, 12:24*pm, "Stig - tame racing driver" <Derek- wrote: Still not working ........ never mind "Shane Devenshire" wrote in ... Hi, Problem: When numbers are enter as text they may not calculate within formulas as they should. *A few formulas will work fine despite the numbers being entered as text. *Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. *You can not tell by checking the Format. *If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. *Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. *Select the text number cell and choose Edit, Paste Special, Add (or Subtract). *This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. cheers, Shane Devenshire "Stig - tame racing driver" wrote: Help!! * i'm going mad how do you Convert numbers stored as text to numbers useing a formula (normaly comes up with and error..... but not today) Thanking you- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert numbers stored as text to numbers
Thank you..........
"David Biddulph" <groups [at] biddulph.org.uk wrote in message ... A number of options worth looking at: Data/ Text to Columns Copy a zero (or an empty cell) and then Edit/ Paste Special/ Add to your range of text numbers (or similarly Edit/ Paste Special/Multiply by 1) -- David Biddulph "Stig - tame racing driver" wrote in message ... Help!! i'm going mad how do you Convert numbers stored as text to numbers useing a formula (normaly comes up with and error..... but not today) Thanking you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert stored numbers into a set naming convention | Excel Worksheet Functions | |||
convert numbers stored as numbers to text | Excel Worksheet Functions | |||
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) |