Concatenation

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

I am trying to link a number of columns together by using
Concatenation. I am able to do one row at a time, but I
have over 175 rows to do. Any ideas on how to do this en
masse.

Thanks -- Shawn
 
If the problem is D1 should be =A1&B1&C1, then copying the
expression down should work in column D for all rows
If the problem is more complicated, post back with a data
sample and desired results.

HTH
 
Thanks for your input. I am doing this sort of string
(under insert/function ( Concatenate) in excel -- in the
text 1 field I am entering the first set of column\row
coordinates(e.g. B4), in text 2 field I am entering the
next set (e.g.B5)and so on until I have the string
complete, I have approx. 20 sets of co-ords to link
together to get the result I want.What I am trying to get
away from is having to do that for each row. i.e. B5&C5...
B6&C6...--B125&C125. I want to be able to put in one
command and have all the columns link properly for each
row> If I make a string in row one that says B&C&D&E&F etc
all I get in my new column is B&C&D&E&F etc, I don't get
the actual info from the column linking.
I am not sure if this makes any sense, thanks again

Shawn
 
Hi Shawn,
Maybe I'm still not getting the picture. What I'm
seeing is something like the following, where you have
text strings in cols B and C and you want another column,
say D, to contain the concatenation of the text in cols B
and C:
A B C D
1 abc def abcdef
2 ghi jkl ghijkl
3 mno pqr mnopqr
4 ... ... ......

If this is true, then you don't really have to use the
CONCATENATE function; in D1 type: =B1&C1. That formula
can be copied down col D for as many rows as you want.

Now, your example is a little confusing, making me think I
don't have the complete picture. It seems you're trying
to string together the content of up to 20 cells, and each
grouping follows a DIFFERENT pattern than the one before.
If that's true, then you have no choice but to do a manual
construction for each case.

If you're trying to do something more elaborate but that
follows a PATTERN, then you need to give me more info.
For example, if you want each successive row to contain
the previous concatenation plus the new one, then you
could, say in D2, type: =D1&B2&B3 to get abcdefghijkl.

Keep in mind that a cell has a limited text capacity (256
chars , I think); if you exceed the limit then you'll get
an error or truncation. I'd like to work through this one
with you. Post back.

HJ
 
Hi HJ - Thanks for your help. I will try to be more
specific. The more I look at this the simpler it becomes
but I can't get it to work.

Column B has first names -- Column C has last names -- ,
I want to join the two together to get column D with first
and last names with a space in between. Presently I have
a list of over 200 names that need to be joined. I am
hoping there is one command I can use to create column D
for all 200+ names.

i.e.
got It got it need it
1 B C D

2 Bob Smith Bob Smith
3 Jane Doe Jane Doe


After I have column D, I will use a concatenation with
other columns B.... T to get a string of text that I will
be used in a drop down on an order form. I seem to be able
to use the concat.. Ok but just can't seem to get this
first\last name thing together.

I am hoping to do the same one command thing with the
concatenation function that joins them all together.

this would be a sample of the whole spread sheet after the
first\last name thing is fixed

1 B C D.....M N..... W

name PH# Cell Title Address B+C+D..
-----------------------------------------------------------

2 Bob Smith (416).. (416). VP 123 Main B+C+D+E...
-----------------------------------------------------------
3 Jane Doe (123).. (123). SMR 45 Main B+C+D+E...
-----------------------------------------------------------

where W is a concat. of all the other columns info that I
use to create a drop menu for an auto fill order form.

re: different pattern - " NO " Column W is always the same
combination of other columns -- row 3 has nothing to do
with any other row. each row is specific to one persons
info only.

Your patience and help is very much needed and appreciated.

Thanks -- Shawn
 
Back
Top