More cells with text,,,,,,,,,,,,,,,,,,,

[TonyK]

The more I keep doing the more questions I have, I really
appreciate the help so far.

Here is my challenge:

(x) is column and what follows is the text and I want to
merge all text to gether excepth for last column because
that will contain the state and I want that seperate.

(a) Detroit (b) MI (c) empty (d) empty (e) empty
(a) Clinton (b) Twp (c) MI (d) empty (e) empty
(a) Saint (b) clair(c) shores (d) mi (e) empty
(a) saint (b) jean (c) luc (d) on (e) canada

I tried with the info above but could not figure it out.

Thanks

Tony

 

[JE McGimpsey]

If you use the MultiCat function from

http://www.mcgimpsey.com/excel/udfs/multicat.html

you can use:

=MultiCat(OFFSET(A1,,,1,COUNTA(A1:E1)-1)," ")

note that this will not work with Canadian provinces if the word Canada
is included.