OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to merge text from 3 columns into new column

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Cymbeline
Newbie
Newbie


Joined: 08 Aug 2006
Posts: 2

PostPosted: Tue Aug 08, 2006 9:16 am    Post subject: How to merge text from 3 columns into new column Reply with quote

Greetings~ I have three columns in my spreadsheet which contain text info which I wish to merge into one new column.

For example:
Col A: Women's Clothing
Col B: Brand (i.e. Gap, Old Navy, Banana Republic)
Col C: Style (i.e. skirts, blouses, pants)

Here is an example of what I would like to achieve in the new column: Women's Clothing:Old Navy:Skirts

Can someone provide me with a forumla to do this?

The "merge" and "consolidate" options are grayed-out so I am unable to use them (providing that would be the correct route anyway). Then, I had thought the text function of concatenate would work, but with a variety of labels in Cols B and C, I don't think it's possible.

Please let me know if my example needs clarification. I know the results I want, but am having difficulty explaining and I am not good with spreadsheets. Shocked

Thank you!

Cym
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Aug 08, 2006 10:04 am    Post subject: Reply with quote

Cym:

You asked:
Quote:
I have three columns in my spreadsheet which contain text info which I wish to merge into one new column.


Assuming, for example, your data is in cells, A1, B1, and, C1, that you want to concatenate ( arrange into a chained list ) . . . and . . . adding, in your example, the : between them, then . . .

This can be accomplished, with either of the two methods below:

Method 1 - use the Calc CONCATENATE function . . . for instance:
Code:
=CONCATENATE( A1; ":"; B1; ":"; C1 )


Method 2 - use an & to combine the text:
Code:
= A1 & ":" & B1 & ":" & C1


I hope this helps, please be sure to let me / us know if this helps.

Sliderule
Back to top
View user's profile Send private message
Cymbeline
Newbie
Newbie


Joined: 08 Aug 2006
Posts: 2

PostPosted: Tue Aug 08, 2006 10:44 am    Post subject: Reply with quote

*happy snoopy dance*

Sliderule - Thank you!!! I chose Method 1 and it works perfectly! I didn't realize I would get the added bonus of the cell numbers corresponding automatically with the row numbers as I copied and pasted the formula into the cells of the new column! (I had thought I would need to do that manually within each cell after pasting)

This is beautiful and will save me absolute mountains of time each day!

Cym Very Happy
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Aug 08, 2006 10:52 am    Post subject: Reply with quote

Cym:

Glad to help . . . and . . . that it will save you mountains of time. I also, really like the way you put it
Quote:
*happy snoopy dance*


Just so you know . . . the Calc Help, might be useful, and, by learning more about spreadsheets, you might find a myriad of uses that you will find useful.

Enjoy Very Happy .

Sliderule
Back to top
View user's profile Send private message
rig514
Newbie
Newbie


Joined: 28 Jun 2008
Posts: 2
Location: Montreal

PostPosted: Sat Jun 28, 2008 7:13 am    Post subject: How can I delete the old columns after merging in a new one Reply with quote

After doing the merge as you stated, I want to delete the old colums. When i do that I get a =REF in the new column containing the merged contents.
How can I keep the new colums data and delete the old ones
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Jun 28, 2008 8:23 am    Post subject: Reply with quote

Copy, then paste special into another column, then delete the first. Do on a COPY of oyur file.

Better [I think]: Obtain and try out CSVed. It will require saving as a CSV file first, but practice makes this software very useful in this and many more instances, and it is quite intuitive once you take the time to read through options.

David.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Sat Jun 28, 2008 10:49 am    Post subject: Reply with quote

Just a few bits to augment David's sound advice:
David wrote:
Copy, then paste special into another column, then delete the first. Do on a COPY of oyur file.
In the Paste special dialog, make sure Formulas is unticked and Text is ticked in the Selection section.
David wrote:
Better [I think]: Obtain and try out CSVed. It will require saving as a CSV file first, but practice makes this software very useful in this and many more instances, and it is quite intuitive once you take the time to read through options.
I quite agree. Anyone working with tabular data will at one time find themselves in a situation where CSVed is useful. In many instances it's been a "lifesaver" for me.

Make sure you get the name right, though. I made a mistake at one time when I tried to find it, thinking it was CSVEdit. That is a different beast entirely, useful for certain website setups, but rather useless on your local workstation Smile
Back to top
View user's profile Send private message
rig514
Newbie
Newbie


Joined: 28 Jun 2008
Posts: 2
Location: Montreal

PostPosted: Sat Jun 28, 2008 12:10 pm    Post subject: Thanks a lot Reply with quote

Thank you
Back to top
View user's profile Send private message
Philip Epstein
Power User
Power User


Joined: 06 Sep 2003
Posts: 52

PostPosted: Sun Jun 29, 2008 11:23 am    Post subject: Re: How can I delete the old columns after merging in a new Reply with quote

[quote="rig514"]After doing the merge as you stated, I want to delete the old colums. When i do that I get a =REF in the new column containing the merged contents.
How can I keep the new colums data and delete the old ones[/quote]

You can also select the old columns, then goto Format ->Column->Hide. This retains your merged data but hides the sources.

Reverse process if you ever need to see the old data by goto Format->Column-.Show.

Refer to OO Help under hiding.
Back to top
View user's profile Send private message Send e-mail
JayVilla
General User
General User


Joined: 05 Oct 2005
Posts: 10

PostPosted: Sat Jul 05, 2008 1:01 pm    Post subject: Reply with quote

How do you merge the whole column at one time? I ran the equation above and only was able to merge one row.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Jul 06, 2008 6:15 am    Post subject: Reply with quote

JayVilla wrote:
How do you merge the whole column at one time? I ran the equation above and only was able to merge one row.


I'm away visiting right now and don't have access to my own computer. However, I repeat: CSVed is a VERY useful program for this sort of thing, and I'd suggest that you give it a try.

David.
Back to top
View user's profile Send private message
rathervague
Newbie
Newbie


Joined: 04 May 2011
Posts: 1

PostPosted: Wed May 04, 2011 10:13 am    Post subject: Reply with quote

Copy the first cell you try the formula in
eg.
Code:
=CONCATENATE(A1; "-"; B1 )


Select all cells in that column and paste.
Then that formula gets applied to all cells in the column.
Back to top
View user's profile Send private message
Marcel Klomp
Newbie
Newbie


Joined: 06 Jul 2011
Posts: 1

PostPosted: Wed Jul 06, 2011 4:28 am    Post subject: Reply with quote

A tip for anyone trying to merge columns where some columns will be empty (for instance merging a familyname prepostion with a familyname). To avoid trailing or leading spaces just use TRIM:
=TRIM(A1 & " " & B1)

And to wrap up all info in the previous posts:
- add two columns behind last column you want to merge
- in first of the new columns type: =TRIM(A1 & " " & B1)
- Copy and paste in all cells under it
- copy whole column,
- select empty column
- Paste special: untick all options, only tick text
- Delete (or hide) the three columns you no longer need
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group