Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Concatenate Function ... Help .... Please!!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using the Concatenate function to string togehter text and formatted numbers (number, 2 decimal places and ( ) used for negative numbers). The whole string is a profit/loss discussion and analysis. The numbers I have referenced lose their formatting in the text string (concatenate function). Is there a way to keep the number format, or assign one while using the Concatenate function??

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes there is: use the TEXT function:
    TEXT(value, formatting code)

    e.g.:
    TEXT(99,"000") displays the number as 099.

    Marc

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    But how do you do it with text Bold and Italics?
    If B1 is bold in size 10 and C1 is in italics in size 8, how to keep the formats when D1 has =Concatenate(B1,C1)

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, that's a different question! I don't think that's possible just using the Concatenate function (or any other function).

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    paelinck,

    I tried the "Text" embedded in the Concatenate function, but it does not seem to be working. I still cannot get the comma for the thousands place, not can I get the parenthesis for the negative numbers. Have any ideas? Do you know of any other funcations that I may use to accomplish the same thing?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you post your formula, I could try to find out the problem. Don't get discouraged. I think you're almost there.
    Marc

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just another idea: the problem might lie with the format string in the TEXT formula. The best way to get the right format string is to edit the format of one of the cells whose format you want to copy, and to select "Custom". The string that is displayed should occur exactly in the format parameter of the TEXT function (between double quotes).

    Marc

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    paelinck,

    Here is the formula I am using:

    =CONCATENATE("Any Company, USA Operations reported a combinded net ",H26, " of ", G26," compared to a budgeted net ",I26," of ",F26," for the month of ",B15)

    Where:
    H26 = Cell referencing "Income" or "Loss"
    G26 = Icome or loss amount (formatted)
    I26 = Cell referencing "Income" or "Loss"
    F26 = Icome or loss amount (formatted)
    B15 = Month/Year

    Thank you for all your help!!




  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I got it to work!! I embedded the DOLLAR formula within the Concatenate format.

    Thank you very much for your time and assistance with this!!

  10. #10
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's easier NOT to use the function, but to use the following. At least, I think it's a lot eaier to read. Not that this helps your dilemma.

    ="Any Company, USA Operations reported a combinded net "&H26"&" of "&G26&" compared to a budgeted net "&I26&" of "&F26&" for the month of "&B15
    ~Anne Troy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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