Text in columns all into one column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

toniasplace - 08 Apr 2004 20:56 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

cdelarosa - 08 Apr 2004 21:03 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Earl Kiosterud - 08 Apr 2004 21:13 GMT

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.

Signature

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

> I use the Data text to columns option all the time when I need to
> seperate things into several columns.
[quoted text clipped - 11 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Cesar Zapata - 08 Apr 2004 21:13 GMT

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

> I use the Data text to columns option all the time when I need to
> seperate things into several columns.
[quoted text clipped - 11 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

toniasplace - 09 Apr 2004 02:02 GMT

> *Hi,
>
[quoted text clipped - 4 lines]
> then just fill down.[/url]
> >[/color] *

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Max - 09 Apr 2004 03:06 GMT

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

Signature

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

> Cesar Zapata wrote:
> > *Hi,
[quoted text clipped - 19 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

toniasplace - 09 Apr 2004 15:57 GMT

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! :)

---
Message posted from http://www.ExcelForum.com/

Reply to this Message

Max - 09 Apr 2004 16:16 GMT

You're welcome!

Signature

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

> 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.
[quoted text clipped - 3 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message