Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Topic Options
#320848 - 31/03/2009 18:30 Excel Help
tfabris
carpal tunnel

Registered: 20/12/1999
Posts: 31602
Loc: Seattle, WA
I have three columns:
First Name, Middle Name, Last Name

Whether or not a given row contains information in each column is somewhat random. Sometimes it might be all three names in the "Last Name" column. Sometimes it might be a business name such as "A Gathering Grove Book Store" all crammed in the first name field. Sometimes it will be a blank last name and only a first and middle name. Somtimes it might be a first name with a blank middle and last name. Etc.

I want to concatenate all three columns together into a single new column called "Name" (obviously with the result containing a space between each of the three values so that you don't get a result of "TonyFabris").

When I try to select the three columns and press the MERGE CELLS button it does not do as I hope it would do. It gives me a complaint box and then makes all the data in the columns disappear.

How do I do this in Excel without hand-editing hundreds of rows of data?
_________________________
Tony Fabris

Top
#320849 - 31/03/2009 18:35 Re: Excel Help [Re: tfabris]
LittleBlueThing
addict

Registered: 11/01/2002
Posts: 612
Loc: Reading, UK
in the top cell of the next column along create an equation like
=a1+" "+b1+" "+c1
drag copy this down all the way (if you care about 2 spaces then wrap the equation in a function to subst " " for " ")
highlight that new column and copy
next column along: paste special "as values"
Delete original 3 columns and tmp equation column
_________________________
LittleBlueThing Running twin 30's

Top
#320854 - 31/03/2009 19:12 Re: Excel Help [Re: LittleBlueThing]
tfabris
carpal tunnel

Registered: 20/12/1999
Posts: 31602
Loc: Seattle, WA
Awesome. I have to use the string concatenation operator (&) instead of the plus sign, but that works. Thanks!
_________________________
Tony Fabris

Top
#320855 - 31/03/2009 19:13 Re: Excel Help [Re: tfabris]
tfabris
carpal tunnel

Registered: 20/12/1999
Posts: 31602
Loc: Seattle, WA
Well, sort of. A lot of the columns start with a space. I'll figure out how to complicate the formula a bit more. Thanks again!
_________________________
Tony Fabris

Top
#320856 - 31/03/2009 19:16 Re: Excel Help [Re: tfabris]
tfabris
carpal tunnel

Registered: 20/12/1999
Posts: 31602
Loc: Seattle, WA
Aha, just wrap it in a TRIM function, that solves double spaces as well as leading and trailing spaces.

=TRIM(B1&" "&C1&" "&D1)
_________________________
Tony Fabris

Top
#320857 - 31/03/2009 19:20 Re: Excel Help [Re: tfabris]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
For the record, "merge cells" combines cells together, not their contents. It's like a span in HTML tables.
_________________________
Bitt Faulk

Top
#320859 - 31/03/2009 19:36 Re: Excel Help [Re: tfabris]
Robotic
pooh-bah

Registered: 06/04/2005
Posts: 2026
Loc: Seattle transplant
Hmm- I would've saved the list as some space delimited file, then reopened it into a single column.
...not sure precisely how that would work, but that would have been my first strategy- to play with the text-to-columns import functions.
_________________________
10101311 (20GB- backup empeg)
10101466 (2x60GB, Eutronix/GreenLights Blue) (Stolen!)

Top
#320863 - 31/03/2009 20:51 Re: Excel Help [Re: Robotic]
LittleBlueThing
addict

Registered: 11/01/2002
Posts: 612
Loc: Reading, UK
cool - glad it worked.

I use that 'trick' a fair amount to solve different problems

_________________________
LittleBlueThing Running twin 30's

Top