Results 1 to 14 of 14

Thread: Concatenate

  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good afternoon

    I have not used the Concatenate formula before and I am getting a strange result in cell N11 for example I am typing =Concatenate(B11," - ",C11) expecting the result to look like SG - IN but instead I get SG in N11 and -IN in P11?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps column N is too narrow to display the complete result?

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stevehocking' post='794537' date='23-Sep-2009 17:00']Good afternoon

    I have not used the Concatenate formula before and I am getting a strange result in cell N11 for example I am typing =Concatenate(B11," - ",C11) expecting the result to look like SG - IN but instead I get SG in N11 and -IN in P11?[/quote]
    In addition to concatenate, you can use "&" syntex.
    Attached Files Attached Files
    Regards
    Prasad

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='794542' date='23-Sep-2009 12:50']In addition to concatenate, you can use "&" syntex.[/quote]
    Thanks Prasad and Hans

    However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

    Editted: helps if I attach the picture!!
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stevehocking' post='794547' date='23-Sep-2009 17:56']Thanks Prasad and Hans

    However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

    Editted: helps if I attach the picture!![/quote]
    It seems that cell are merged & formatted in a particular manner. try de-merging cell & format in usual way.
    Regards
    Prasad

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='794548' date='23-Sep-2009 13:35']It seems that cell are merged & formatted in a particular manner. try de-merging cell & format in usual way.[/quote]
    Hi Prasad

    The cells are not split until I type in either of the formulas and it then gives the appearance of splitting the cells but I don't think they are becaus I can tab between the cells as normal, I even went right across to column AZ which is nowhere near the data but it is still doing the same and taking out the cell lines for 3 or 4 cells?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stevehocking' post='794549' date='23-Sep-2009 18:15']Hi Prasad

    The cells are not split until I type in either of the formulas and it then gives the appearance of splitting the cells but I don't think they are becaus I can tab between the cells as normal, I even went right across to column AZ which is nowhere near the data but it is still doing the same and taking out the cell lines for 3 or 4 cells?[/quote]
    Is it possible to attache a copy of sheet instead of image?
    Regards
    Prasad

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I could replicate the effect by putting the right thing in B11: SG followed by lots and lots of space characters.
    What is actually in B11 ?

    [attachment=85655:concatenate.gif]
    Attached Images Attached Images
    Regards
    John



  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='794550' date='23-Sep-2009 13:48']Is it possible to attache a copy of sheet instead of image?[/quote]
    Hi John

    Column B & C are populated by 2 letter country codes, for example NL, US, UK, DE, IN, SG etc. All I am putting in is either

    =Concatenate(B11," - ",C11) or as Prasad suggested =B11&" - "&C11 and both behave in the same way.

    Having looked a bit more it may be that this is a huge file downloaded as a CSV for speed and saved as a .xls and maybe there is some hidden formatting going on but I can't see it. I have tried both methods in a clean workbook and they work fine but even copying the data out of the errant book into a new one does not solve it.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='stevehocking' post='794578' date='23-Sep-2009 17:29']Hi John

    Column B & C are populated by 2 letter country codes, for example NL, US, UK, DE, IN, SG etc. All I am putting in is either

    =Concatenate(B11," - ",C11) or as Prasad suggested =B11&" - "&C11 and both behave in the same way.

    Having looked a bit more it may be that this is a huge file downloaded as a CSV for speed and saved as a .xls and maybe there is some hidden formatting going on but I can't see it. I have tried both methods in a clean workbook and they work fine but even copying the data out of the errant book into a new one does not solve it.[/quote]

    Have you tried =CONCATENATE(TRIM(B11), " - ", TRIM(C11)) ?

  11. #11
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Were does the cursor appear if you click on the cell and then press your F2 key? If it does not appear immediately after the last letter, then you have spaces at the end of the field. Or highlight your column, go to Format / Column> / Autofit Selection.

    Gfamily's fomulas can be trimmed down to:
    =TRIM(CONCATENATE(B11, " - ", C11))

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stevehocking' post='794547' date='23-Sep-2009 20:26']Thanks Prasad and Hans

    However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

    Editted: helps if I attach the picture!![/quote]


    I see no problem with the formula you posted.
    Care to share a sample of your copy? It would be some other issue than the formula itself.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The most likely cause of the problem is that the cells in column B contain trailing spaces. The formulas suggested by Gfamily and mbarron should take care of that.

  14. #14
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I just recreated it with no problem, including trailing spaces, which just appeared in the result when they were added to the source cells.

    I did use the Function Arguments dialog box -- typed =concatenate(
    and then clicked on the fx Insert Function button to the left of the formula bar and completed the formula using the Function Arguments box. Perhaps that might work better.

    Good luck!

Posting Permissions

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