Text in columns all into one column

  • Thread starter Thread starter toniasplace
  • Start date Start date
T

toniasplace

I use the Data text to columns option all the time when I need t
seperate things into several columns.

How do I do the opposite. I want to take the text that is in severa
columns and put it into one column?

or how do I take text that is in A,B,C,D columns and add them all int
one column A?

Thanks in advance,

Toni
 
In cell a2 type

=a1&b1&c1&d1

If you need spaces to separate do the following

=a1&" "&b1&" "&c1&" "&d1

Then copy cell a2 and pastespecial/values into a1.

Carlo
 
Tonia,

In another column, use one of these:

=A2 & B2 & C2 & D2
=A2 & " " & B2 & " " & C2 & " " & D2

These formulas will require that the stuff in cols A-D remain. If you want
to make this permanent, select the column containing the formulas, and into
another column, Paste Special - Values. Now you will have hard values (not
longer calculated from what's currently in A-D). You can do away with A-D
if you wish.
 
Hi,

one way is to use the concatenate function. lets say I want to put all
the info in cells b1,c1,d1 so in A1 i'll insert =CONCATENATE(B1,C1,D1)
then just fill down.


Bye,

Cesar Zapata
 
Cesar said:
*Hi,

one way is to use the concatenate function. lets say I want to pu
all
the info in cells b1,c1,d1 so in A1 i'll inser
=CONCATENATE(B1,C1,D1)
then just fill down.[/url] *
Click to expand...
[/QUOTE]


I tried the formula and it worked perfectly.

Now, how do I take a column that has 100 rows of information in it an
add them to one column of 100 rows. I want to take my customer
addresses of City, State, and zip codes and add that to there name i
one cell. But, I would like to do that all at once.

Is this possible :(


Thank
 
Suppose your table is in cols A to D,
col headers in row1, data from row2 down, viz.:

Name1 City1 State1 ZipCode1
Name2 City2 State2 ZipCode2
etc

Put in E2: =A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2

Copy E2 down

Select col E and format to wrap text
(via Format > Cells > Alignment tab > Check Wrap text > OK)

Col E will give you what you want

If required to freeze the values in col E,
do an in-place copy > paste special > values > OK

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
toniasplace > said:
Cesar said:
*Hi,

one way is to use the concatenate function. lets say I want to put
all
the info in cells b1,c1,d1 so in A1 i'll insert
=CONCATENATE(B1,C1,D1)
then just fill down.[/url] *
Click to expand...
Click to expand...


I tried the formula and it worked perfectly.

Now, how do I take a column that has 100 rows of information in it and
add them to one column of 100 rows. I want to take my customers
addresses of City, State, and zip codes and add that to there name in
one cell. But, I would like to do that all at once.

Is this possible :(


Thanks
[/QUOTE]
 
Thanks for all the replies. I was able to make it work using the
formulas. It has taken me forever to find a why to fix that problem.


You guys are GREAT! :)
 
Back
Top