append data to end of cell in Excel

[Kelli Ward]

Hi All,

Does anyone know if it is possible to add data to the end of a group of cells in Excel 97?

Basically I have over 1200 name entries in a column I would like to add a domainname to the end of without doing it individually.

Thanks,
Kel

[Roger Faucher · Accepted solution]

Hi Kelli:

Let's say you have a list of domain names in column E in a worksheet and you want to append {.com} to each cell. You'll need two blank columns to the right, so if they don't already exist, insert them (I assume you already saved your worksheet, just in case). In the first cell in column F, insert the value .com. Now, left mouse click in that cell and drag the mouse pointer down to the end of your list and release the mouse button. Select Edit/Fill/Down. Now all cells in Column F (in the Column E range) should be populated with .com. In the top cell in Column G, enter the formula =E1&F1. Now do the same thing in Column G that you did in Column F (left mouse click in the cell where you just entered the formula and drag the mouse pointer down to the bottom of the range, then Edit/Fill/Down. At this point, Column G should contain your desired result. You should be able to delete Columns E and F and have what you want.

Hope this simplistic example helps.

Roger

[Ron Kinner]

Roger,

Don't you have to select column G and do Edit Copy then Edit Paste Special and choose Values before you can delete columns E & F? Seems like if you delete E & F without doing that you will get a #REF! error.

Ron

[Kelli Ward]

Hi,
Thanks for the tip.
It almost worked flawlessly.
After filling down the data in Column G, if E and F are deleted, the forula loses its reference, and changes the data to #REF.
To complete this, Highlight Column G , click Copy, Then Paste Special (using Column H, for example) Tick on Value and it will convert the formulas to raw text which is what I needed.

Much easier than adding each bit of data manually. Whew! Thanks for the help. Knew there had to be a way.
Kel

[Kelli Ward]

Oops, forula is supposed to formula.

Thanks for the addition Ron, I was able to find that part.
(Rest of the answer added in case anyone else ever needs it and for the search engine)
Kel

[Jon Finley]

hmmmm.. You also could have do it by going over to the last column ad using the CONCATENATE function:

i.e.

IF the domain is at column G, then at H place the following:

=CONCATENATE(G1,".com")

then copy it down and convert it to a value (if needed).

Using the MID, LEFT, RIGHT and FIND functions with CONCATENATE give you lots of power within Excel working with text strings.


Jon