Concatenation Function Question (Excel 2003)

[TheDon]

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.........

[sdckapr]

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

Steve

[Jezza]

Alternatively:

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

[sdckapr]

<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

[Jezza]

<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.

[Rudi]

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>

[Rudi]

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

[HansV]

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.

[Rudi]

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

[sdckapr]

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

[Rudi]

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>

[sdckapr]

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

[Rudi]

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

[Jezza]

No

[HansV]

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