Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pasted values that paste as "number stored as text"
I am copying numeric information that is a formula and dropping it onto a
part of the spreadsheet that is pre-formatted. When I paste I do a paste special / values but it is pasting the results as text, I have to then go and change the result "text stored as a number" to "convert to number." I tried to paste special / values and number formats but it still pastes as text . . . is there any known way to shortcut the step? I have formatted the formula data (what I'm copying) as a number but it still pastes as text. Thx! mm |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pasted values that paste as "number stored as text"
2 things:
1 - confirm the value you're copying is formatted as something other than text; 2 - why bother with paste special? Just Paste. "smartgal" wrote: I am copying numeric information that is a formula and dropping it onto a part of the spreadsheet that is pre-formatted. When I paste I do a paste special / values but it is pasting the results as text, I have to then go and change the result "text stored as a number" to "convert to number." I tried to paste special / values and number formats but it still pastes as text . . . is there any known way to shortcut the step? I have formatted the formula data (what I'm copying) as a number but it still pastes as text. Thx! mm |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pasted values that paste as "number stored as text"
it's more than a bit convoluted but here goes:
1. I'm copying table data from a website and when I paste it in excel it has an extra space character at the end of each number . . . 2. So when I paste it, I have a replica of the data that reads what I just pasted and has a formula that removes the space. This is the data I am copying . . . 3. So from here I copy and paste as a value in *another* column of replicas that are formatted how the data should look (font, size, etc) . . . 4. This third replica, what I'm trying to get to "auto-format" as a number (rather than as a number stored as text) is what is pasted into a StarOffice spreadsheet, which my client insists upon using. Clear as mud?? Yeah, me too . . . :-) "Bob Umlas, Excel MVP" wrote: 2 things: 1 - confirm the value you're copying is formatted as something other than text; 2 - why bother with paste special? Just Paste. "smartgal" wrote: I am copying numeric information that is a formula and dropping it onto a part of the spreadsheet that is pre-formatted. When I paste I do a paste special / values but it is pasting the results as text, I have to then go and change the result "text stored as a number" to "convert to number." I tried to paste special / values and number formats but it still pastes as text . . . is there any known way to shortcut the step? I have formatted the formula data (what I'm copying) as a number but it still pastes as text. Thx! mm |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pasted values that paste as "number stored as text"
What formula(s) do you have in step 2 to remove the space? If it
involves TRIM, or SUBSTITUTE, or LEFT or RIGHT you will actually be returning a text value, and so that propagates through your other steps. You might need to just add *1 to your formulae in step 2 to convert to a number at that point. Hope this helps. Pete On Jul 1, 9:23*pm, smartgal wrote: it's more than a bit convoluted but here goes: 1. *I'm copying table data from a website and when I paste it in excel it has an extra space character at the end of each number . . . 2. *So when I paste it, I have a replica of the data that reads what I just pasted and has a formula that removes the space. *This is the data I am copying . . . 3. *So from here I copy and paste as a value in *another* column of replicas that are formatted how the data should look (font, size, etc) . . . 4. *This third replica, what I'm trying to get to "auto-format" as a number (rather than as a number stored as text) is what is pasted into a StarOffice spreadsheet, which my client insists upon using. Clear as mud?? *Yeah, me too . . . *:-) "Bob Umlas, Excel MVP" wrote: 2 things: 1 - confirm the value you're copying is formatted as something other than text; 2 - why bother with paste special? Just Paste. "smartgal" wrote: I am copying numeric information that is a formula and dropping it onto a part of the spreadsheet that is pre-formatted. *When I paste I do a paste special / values but it is pasting the results as text, I have to then go and change the result "text stored as a number" to "convert to number." I tried to paste special / values and number formats but it still pastes as text . . . is there any known way to shortcut the step? *I have formatted the formula data (what I'm copying) as a number but it still pastes as text. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
Text stored in a cell is displayed as a string of "#####..." | Excel Discussion (Misc queries) | |||
How do I ignore the "Number stored as text" error message permane. | Excel Discussion (Misc queries) | |||
how to converts a number to text eg. "2" become "two" | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |