Insert data into exsisting data

**blbeta** · 04-17-2007, 02:58 PM

I have three columns of existing data.

Column A I want to put "PU" before everything in that column.

Column C&D I want to put "." before that last two digits. These are dollar amounts but that text file they came from had no decimal before the cents. I tried to make it go to 2 decimal places but as you probably know that just added .00 to every number and that is not what I need.

This could be done manually but doing it to 110,000+ cells for each column would take a bit of time.

I am assuming there is a simple way to do this, but my excel knowledge is severely lacking.

Thanks ahead of time.

**Paul** · 04-17-2007, 03:11 PM

You'll need some helper columns to get this data setup, but then you can copy>pastespecial>values on top of the original data and delete the helper columns afterward.

For column A's values, insert a column to the right of column A and in B1 (if your data starts in A1, otherwise start in B#) use the formula:

="PU"&A1

Again, if your data starts in A5, for example, put this formula in B5 instead and change it to ="PU"&A5.

Once that one cell is done, double-click on the lower right corner of that cell and it should fill the formula down column B for as many rows of data you have in column A. Finally, select the new data in column B and choose Edit > Copy. Select the data range in column A and click Edit > PasteSpecial > Values. You can then delete the helper column B.

As for updating columns C & D, insert two helper columns to the right of D (or use two blank columns further right in your worksheet). In the first column (again, assuming your data starts in C1), use the formula

=LEFT(C1,LEN(C1)-2)&"."&RIGHT(C1,2)

This time, instead of double-clicking on the lower right corner of that cell, click down on the lower right corner and drag one column to the right. This will adjust the formula automatically so that column D's values are correct. Now to get the entire columns updated, with both new cells still selected, click down on the lower right corner of your second cell and drag downward as many rows as exist in columns C and D (then let go of the mouse button).

Now all you have to do is select all of your new data and Copy, then select your column C and D data and choose PasteSpecial > Values again. You can then delete those two helper columns.

Hope that helps.

UPDATE: If you use =(LEFT(C1,LEN(C1)-2)&"."&RIGHT(C1,2))*1 it will convert your text values into numeric values that can be formatted like currency.

**blbeta** · 04-18-2007, 06:41 PM

Thanks a ton that worked great!

There was something a bit strange to me though with the auto fill by double clicking. It worked flawlessly with the first column, apparently because the column to the right was filled as well.

Now with the C&D column there was data in most of the cells in E, but not all. I started C&D the way you mentioned by dragging but 60,000+ rows was gonna take a bit. With F1&G1 highlighted, formula copied as mentioned by you, I used the double click. This seemed to work but would stop anywhere a cell in E had no data. I'd fill in the data, then highlight the last two done in F&G then double click and it would continue until it found data missing in E again.

Not a big deal, but I found it strange as the formula didn't appear to have anything to do with column E. This is in Excel 2007 if that helps. Not sure if you'd run into before or not.

Again, many thanks for the help.

**Paul** · 04-18-2007, 09:11 PM

The 'double-click' method looks to the column that is to the left of the column you're double-clicking. Since column E happened to be that column, when you double-clicked, it will only fill down until the next empty cell in the column to the left and then stop, basically making the assumption that there's a logical reason why there is a break in your data. Sometimes Excel is wrong in its assumptions, just like us humans.

Glad to see it worked out for you, though.

Insert data into exsisting data

LinkBack

Thread Tools

Rate This Thread

Display

Insert data into exsisting data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions