Question Locked

Excel 2003 - Concatenate Formula

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Greetings!

I'm using the CONCATENATE formula to put together a number of items, including test, column values, and ASCII characters in order to build a comma-delimited file in a particular format.

The one column that is giving me trouble is one which needs to start with a " and have several line feeds within it.

Here's part of the code:

=CONCATENATE(G1," Mhz Offset: ",H1,CHAR(10),I1,CHAR(10),"Tone:" ,K1," Hz",CHAR(10),"Notes: ",J1,CHAR(10))

This is designed to produce a multi-line output that looks something like this:

444.3 Mhz Offset: +
N5TKG
Tone:131.8 Hz
Notes: oel

As you can tell, all the data is on the same row. It works great, except for the fact that when I go to copy the row to a different row, I don't get any relative reference changes. All the copied rows still refer to the first row. In other words, I would get 2,000+ lines exactly the same, rather than generating unique data for each row.

Is this a quirk of the CONCATENATE function or have I missed some setting somewhere?

Any thoughts would be most appreciated!

Thanks!

2 replies

by bizzo

If you copy the cell that contains the formula, and paste into another cell, then it will work.

If you just copy the text within the cell, then it will paste the text only, ie. referring to the data in row 1.

This discussion is closed to new replies.