• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying and pasting numbers as text

Grumpy88

Member
Hi. Getting very frustrated and would really appreciate some help from the Excel ninja gurus please!

I have an Excel 2010 spreadsheet being used as a database, which includes inter alia a range of columns containing numbers. When originally entering the data I left blank cells in these columns, because there are many instances where the same set of numbers applies to more than one row, but I didn't want to repeat the numbers in each row as there would then be double- or triple-counting of those numbers when I sum them for reporting purposes.

This system has worked well for me up until now, but recently when analysing the data using pivot tables I have discovered a weakness: when double-clicking a pivot table to drill down into the underlying data, only those rows containing the numbers are displayed. Thus the other rows to which those numbers also apply (but whose number columns I left blank so as not to double-count the same numbers) are omitted from the pivot table source data filtering. Hope that description of my problem makes sense!

In an effort to address this I am now trying to complete all the blank cells with the applicable numbers, but in text format so that they don't interfere with calculations by counting each set of numbers more than once. As typing in each number manually with an apostrophe in front of it is very labour-intensive, and creates too much opportunity for human error, I am trying to copy and paste instead.

Unfortunately I haven't found a way to get Excel to do this for me - all paste actions result in the blank cells being completed with numbers, not as text format. I have formatted the blank cells to be Text, and used Paste Special - Values, but neither makes any difference - Excel still pastes the copied data as numbers, which then throws out my calculations. I have also been unable to succeed using macros.

Can anyone please offer a solution for copying and pasting my numbers as text, so that I don't have to retype everything manually (you'd think Excel would cater for this, what with all its multitude of different paste options on offer)?

Thanks so much.
 
Would it be fine if you do repeat those numbers in the data but in the report take an Average instead of Sum?
 
Hi.

No, using Sum is a vital part of compiling the reports. Average will unfortunately not produce the same results.
 
Somehow feel that you should look at a solution on your report side of things rather than data. I'm sure there will be a way to get the results with all numbers populated.

Having text and numbers in the same columns and that too when it acts like a DB doesn't seem like a good option.

As far as your specific query though, the only option I can think of is to have a new column that either produces a number or text and then copy this into your desired column in the table.

Number as Text.png
 
Thanks Vivek.

You're probably 100% right - I know I'm currently violating a lot of general database principles with my speadsheet setup, but I've never managed to get Access to work satisfactorily for me and I like the formula options that Excel gives me.

I'll definitely give your idea of adding extra columns to hold the text versions of the numbers some serious thought. It certainly will save me a ton of work compared to trying to convert the existing numbers into text manually.

Considering all that it can do, I can't believe Excel doesn't cater for a numbers-to-text paste option though. Would be such an easy solution!
 
Back
Top