Concatenation

[MichaelS]

I have a long spreadsheet (1780 lines) with columns A through G.

On all lines where there is a value for col. D, I would like to concatenate
columns D, E, and F into column G. If my formula is =CONCATENATE(D,E,F) it
doesn't work because I don't have the line numbers, but I can't format every
line with a separate formula. Is there a wildcard for the line numbers? Can I
have this formula apply to all lines of column G?

 

[Melissa]

use this formula in column G:
=if($D1<>0,concatenate(D1,E1,F1),"")

What this means is that if D is not blank, then concatenate. If it is
blank, do nothing.

 

[MichaelS]

Thanks for your reply Melissa.

Is there a wildcard character for the line numbers, or will I need to enter
this formula with specific line numbers for all 1780 lines of column G?

 

[mulji.hafeez]

I have a long spreadsheet (1780 lines) with columns A through G.

On all lines where there is a value for col. D, I would like to concatenate
columns D, E, and F into column G. If my formula is =CONCATENATE(D,E,F)it
doesn't work because I don't have the line numbers, but I can't format every
line with a separate formula. Is there a wildcard for the line numbers? Can I
have this formula apply to all lines of column G?

Hey Michael

If all Rows in Column D have values, input this formula in cell G1 and
then just fill down and the line numbers will change on their own.

=CONCATENATE(D1,E1,F1)

If not all Rows in Column D have values, input this formula in cell G1
and fill down.

=IF(ISTEXT(D1),CONCATENATE(D1,E1,F1),"")

 

[Melissa]

I'm not sure I understand your question entirely but you should just copy the
formula in all rows of column G.
Perhaps you can put up some sample data with the desired result? So I can
better understand what you would like to achieve.

 

[MichaelS]

Col D is a web link, Col E is an ID number, and Col F is a data number (E and
F are always the same, but D is variable). So I might have this:

Col D: http://some.com/product+name1
Col E: &id=100
Col F: &data=2500

and they should concatenate into this:
http://some.com/product+name1&id=100&data=2500

Eventually every line will have a value in Col D, but right now some lines
have a value and others don't. The existing formula is =CONCATENATE(D2,E2,F2)
with a different line number for each value in Col G that is filled in. Lines
where there is no value have no formula. Can I have a wildcard formula for
every line of Col G that will automatically fill in when a value is added to
Col D?

 

[Melissa]

Hi Michael,
the IF + CONCATENATE formula would work for your case, methinks. So if you
use the formula I gave earlier in column G and copied down, these are the
results:
A B C D E F G
1 a b c x y z xyz
2 a b c y z

When you finally input a value in D2, G2 will show "xyz"

Am I in sync with your needs?

 

[MichaelS]

Ah, I see. Somewhere along the line someone pasted data into Col G, which
must have interrupted the formula. So I need to restore the continuity of the
formula to get it back. Thanks for this important clue!

 

[MichaelS]

Yes, but after reading the post from (e-mail address removed) a lightbulb
turned on above my head and I started to check the entries in Col G. They
were all formulas up to a point, then there was data. I think I need to
change that data back into a formula to restore the continuity that was
interrupted by data. Thanks for your help Melissa.