Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers NOT working
Hi,
I'm using Excel 2003. I have imported a table of data using cut and paste. The numerical data reports back as TYPE = 2 i.e. text. I have tried all the techniques outlined on this forum and the knowledge base and none appear to work. So I have use the copy/paste special with a copied number (0 or 1, Add or Multiply) I have used Data/Text to Columns etc. etc. Any ideas? regards, bon |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers NOT working
Hi Bon,
================================================== =============== Your Numbers don't behave (like numbers) Niek Otten, May 11, 2006 Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. In short: Your Numbers look like Numbers, but they really are Text. Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text! Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's ISNUMBER() function to check your cells; maybe you solved your problem in the first step! · Format an empty cell as Number. Enter the number 1 in it. EditCopy. Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() function to remove them · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function to remove most of them · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use David McRitchie's TRIMALL() function to remove them. It can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================== =============== "BON" wrote in message ... | Hi, | | I'm using Excel 2003. I have imported a table of data using cut and paste. | | The numerical data reports back as TYPE = 2 i.e. text. | | I have tried all the techniques outlined on this forum and the knowledge | base and none appear to work. | | So I have use the copy/paste special with a copied number (0 or 1, Add or | Multiply) | I have used Data/Text to Columns etc. etc. | | Any ideas? | | regards, | bon |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers NOT working
Copy a blank cell, then right click on your data, paste special, select Add,
click OK "BON" wrote: Hi, I'm using Excel 2003. I have imported a table of data using cut and paste. The numerical data reports back as TYPE = 2 i.e. text. I have tried all the techniques outlined on this forum and the knowledge base and none appear to work. So I have use the copy/paste special with a copied number (0 or 1, Add or Multiply) I have used Data/Text to Columns etc. etc. Any ideas? regards, bon |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers NOT working
Just use the function =VALUE(text)
Make a colomn on the side of your number/text number. Insert function =VALUE(text) where (text) is reference to the cell were the textnumber is. It makes text into number; copy function down to the rest if needed. "BON" wrote: Hi, I'm using Excel 2003. I have imported a table of data using cut and paste. The numerical data reports back as TYPE = 2 i.e. text. I have tried all the techniques outlined on this forum and the knowledge base and none appear to work. So I have use the copy/paste special with a copied number (0 or 1, Add or Multiply) I have used Data/Text to Columns etc. etc. Any ideas? regards, bon |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Numbers NOT working
On Thu, 23 Nov 2006 08:38:02 -0800, BON wrote:
Hi, I'm using Excel 2003. I have imported a table of data using cut and paste. The numerical data reports back as TYPE = 2 i.e. text. I have tried all the techniques outlined on this forum and the knowledge base and none appear to work. So I have use the copy/paste special with a copied number (0 or 1, Add or Multiply) I have used Data/Text to Columns etc. etc. Any ideas? regards, bon I assume you have an entry that looks like a number, but you cannot convert to text, and that there are no non-numeric characters visible in the cell. Try this formula: =--TRIM(SUBSTITUTE(A1,CHAR(160),"")) where A1 is the cell reference you wish to convert. Imported HTML data frequently has a <nbsp (no break space) appended. The above will remove it. If that doesn't work, post back and I will post a VBA method. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Sum multiple cells with different numbers and text. | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Using numbers as numbers in a cell having text | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |