Concatenation Function Question (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

=B1&” “&a1&” “&c1&”, “&d1

Steve

Reply | Quote

Alternatively:

=CONCATENATE(B1,” “,A1,” “,C1,”,”,D1)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

>>> but I am not very good at tpying (I can see that!!!! – )

Reply | Quote

Steve…you are famous for creating new functions. You assisted me multiple times with customised VLookups etc!! How about creating a CONCATENATE_RANGE() function.

Reply | Quote

I’m not Steve, but here is such a function:

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

Use like this:

=Concatenate_Range(A1:A10)

or

=Concatenate_Range(A1:A10,", ")

If you put the functiion in your Personal.xls, you must use Personal.xls!Concatenate_Range.

Reply | Quote

Absolutely stunning Hans. It works like a dream.
Great example!

Reply | Quote

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

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…

Steve

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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?

cheers

Reply | Quote

No

Reply | Quote

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

Reply | Quote

Not directly, but it does have the following useful abilities:
1. It works over VPN, so you don’t have to go to work at all.
2. If you enter “I quit” in cell A1 and then use the mailto ability, you don’t have to worry about traffic!

Reply | Quote

Cheers rory and Hans.

Reply | Quote