concatenate text and numbers

  • Hi,
    I need a little help with this if anyone knows. I have 6 different columns wich contains text and values and i would like to write the intire text to one collumn and add . I already made formula


    =CONCATENATE(A2;"(";ROUND(B2;4)*100;"%";")";"+";C2;"(";ROUND(D2;4)*100;"%";")";"+";E2;"(";ROUND(F2;4)*100;"%";")";) to merge the columns and add the "()" , "+" and "%". Now the problems accure in second row in wich i have only one value


    and result is "UF5166(100%)+ (0%)+ (0%)". I would like it to be without "+ (0%)+ (0%)". I know i can just copy and paste it as values in different column and replace "+(0%)" with empty space, but i would like to do it if possibel with within the


    above formula or better with vba?


    thanks


    [TABLE="class: grid, width: 1500"]

    [tr]


    [td]

    n1

    [/td]


    [td]

    pons1

    [/td]


    [td]

    n2

    [/td]


    [td]

    pons2

    [/td]


    [td]

    n3

    [/td]


    [td]

    pons3

    [/td]


    [td]

    this is what i get with my formula with using concatenate

    [/td]


    [td]

    this is whta i would like to get

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    UF3918

    [/td]


    [td]

    20,5492974102892%

    [/td]


    [td]

    UF5160

    [/td]


    [td]

    58,0246196725119%

    [/td]


    [td]

    UF5166

    [/td]


    [td]

    21,4260829171989%

    [/td]


    [td]

    [TABLE="width: 320"]

    [tr]


    [TD="width: 320"]UF3918(20,55%)+UF5160(58,02%)+UF5166(21,43%)

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    UF3918(20,55%)+UF5160(58,02%)+UF5166(21,43%)

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    UF5166

    [/td]


    [td]

    100%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    [TABLE="width: 320"]

    [tr]


    [TD="width: 320"]UF5166(100%)+ (0%)+ (0%)

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    UF5166(100%)

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    sorry i didn know that both forums are conected. i tried to delete the thread in other forum but i cant log on to it, it shows database error.
    here is the link to the other forum.


    http://www.mrexcel.com/forum/e…atenate-text-numbers.html



    [TABLE="width: 1299"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: concatenate text and numbers


    Try this:

    Code
    1. =SUBSTITUTE(CONCATENATE(IF(TRIM(A2)="";"";CONCATENATE(A2;"(";ROUND(B2;4)*100;"%";")"));IF(TRIM(C2)="";"";CONCATENATE("(";ROUND(E2;4)*100;"%";")"));IF(TRIM(F2)="";"";CONCATENATE("(";ROUND(G2;4)*100;"%";")"; )));")(";")+(")

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: concatenate text and numbers


    [cp]*[/cp]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: concatenate text and numbers


    Maybe i wrote a bit missleading, sorry.
    If i put your formula in first row its not ok beucase it delets the n1, n2 and, n3 (i get "UF3918(20,55%)+(58,02%)+(21,43%)" ) . All has to remain. The firs and the second row.


    i would like to write in the first row named "this is whta i would like to gete such formula that gives me result in way:
    First row "UF3918(20,55%)+UF5160(58,02%)+UF5166(21,43%)"
    Second row "
    UF5166(100%) "

  • Re: concatenate text and numbers


    Try this instead (you will need to go through and change all , to ; for this to work - I don't have time to do that for you right now).


    Code
    1. =SUBSTITUTE(CONCATENATE(IF(TRIM(A2)="","",CONCATENATE(A2,"(",ROUND(B2,4)*100,"%",")")),IF(TRIM(C2)="","",CONCATENATE(C2,"(",ROUND(E2,4)*100,"%",")")),IF(TRIM(F2)="","",CONCATENATE(F2,"(",ROUND(G2,4)*100,"%",")",))),")U",")+(U")

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: concatenate text and numbers


    You're welcome! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules