Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2005
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Concatenation Function Question (Excel 2003)

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

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenation Function Question (Excel 2003)

    Alternatively:

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>)
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards,
    Rudi

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards,
    Rudi

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenation Function Question (Excel 2003)

    No
    Jerry

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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 LastLast

Posting Permissions

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