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.
|