# Thread: Concatenation Function Question (Excel 2003)

1. ## Concatenation Function Question (Excel 2003)

I have information in 4 cells. Need to a concatenation formula in E1.

Cell A1 Gates
Cell B1 Bill
Cell C1 VP
Cell D1 Rochester

I need a formula in cell E1. Want E1 to say - Bill Gates VP, Rochester

What is the best formula.

Thanks in advance.........

2. ## Re: Concatenation Function Question (Excel 2003)

=B1&" "&a1&" "&c1&", "&d1

Steve

3. ## Re: Concatenation Function Question (Excel 2003)

Alternatively:

=CONCATENATE(B1," ",A1," ",C1,",",D1)

4. ## Re: Concatenation Function Question (Excel 2003)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I hate that function. It is difficult to spell. Also, unlike the SUM function, which can take values individually or as a range, concatenate can not be used to concatenate a range. The each must be entered individually, which makes it not much of an improvement over using "&".

Steve

5. ## Re: Concatenation Function Question (Excel 2003)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I agree, but I like to provide options . I do your option a lot of the time, but I am not very good at tpying so the function allows users to go through the Function Argument Wizard.

6. ## Re: Concatenation Function Question (Excel 2003)

Steve...you are famous for creating new functions. You assisted me multiple times with customised VLookups etc!! How about creating a CONCATENATE_RANGE() function. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

7. ## Re: Concatenation Function Question (Excel 2003)

>>> but I am not very good at tpying (I can see that!!!! - <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

8. ## Re: Concatenation Function Question (Excel 2003)

I'm not Steve, but here is such a function:
<code>
Public Function Concatenate_Range( _
oRange As Range, _
Optional strSeparator As String) As String
Dim ocell As Range
For Each ocell In oRange.Cells
Concatenate_Range = Concatenate_Range & strSeparator & ocell.Value
Next ocell
Concatenate_Range = Mid(Concatenate_Range, Len(strSeparator) + 1)
Set ocell = Nothing
End Function
</code>
Use like this:
<code>
=Concatenate_Range(A1:A10)
</code>
or
<code>
=Concatenate_Range(A1:A10,", ")
</code>
If you put the functiion in your Personal.xls, you must use Personal.xls!Concatenate_Range.

9. ## Re: Concatenation Function Question (Excel 2003)

Absolutely stunning Hans. It works like a dream.
Great example!
<img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

10. ## Re: Concatenation Function Question (Excel 2003)

I see Hans had already created one.

I actually have a similar one already in my Personal.xls file. I called it "Grouper", since it is shorter than something "Concatenete_Range".

<img src=/S/ranton.gif border=0 alt=ranton width=66 height=37>It is a peeve to me that:
"+" is akin to "&" for adding numbers/strings.

Also SUM(A1,A2,A3) is akin to concatentat(A1,A2,A3) for doing the same

But:
SUM(A1:A3) works to sum the range, but concatentat(A1:A3) does not work to concatenate the range

It is just one of the things about XL that does not make sense to me... <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37>

Steve

11. ## Re: Concatenation Function Question (Excel 2003)

It sound as if you have some pretty nifty (secret) functions floating around in your Personal.xls file. Are there any more in there that you care sharing?
I respect your privacy though! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

12. ## Re: Concatenation Function Question (Excel 2003)

As with most people, I have specific ones for task as well as one's I create for various reasons. Some I create just to post here and never save as I have no use for them. Some I have created for one purpose and never use it again

if there is something you need, we will gladly try to provide them. Any particular requests?

Steve

13. ## Re: Concatenation Function Question (Excel 2003)

No immediate requests currently (tx for the offer!), except this:
A function that will transport me to work and back home without having to fight the rush-hour traffic will do just great. Is Excel capable of that? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

cheers

No

15. ## Re: Concatenation Function Question (Excel 2003)

You will have to wait for Excel 2371 and install the Scotty.xla add-in. It will contain a BEAM_ME_UP function.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•