Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formating a concatenate function

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello. When using the "&" character to join a text (say, in cell A1) with a value from another cell, is there a way to format the value in cell A1 after it has been joined with the text? For example, is there a way to format the number in cell A1 to appear as (1,000), instead of the default -1000? Thanks for you help!

  2. #2
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use the Text formula:

    TEXT(value,format_text)

    say you want a comma,

    &TEXT(+D6,"#,###")

    Corticus

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Corticus. Can you tell me what the format code is when you want to show a negative number with parentheses, instead of a minus sign? For example, (1,234) instead of -1234. Thanks again...

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 08:27, Yepper wrote:
    Thanks, Corticus. Can you tell me what the format code is when you want to show a negative number with parentheses, instead of a minus sign? For example, (1,234) instead of -1234. Thanks again...
    Hi Yepper:
    You can use FORMAT|CELLS|NUMBER and from the dialog box pick (1234.00) and click OK.


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello, Yogi. Can you tell me what the "format code" (such as #,###)is that will enable me to have a negative number shown with parentheses and commas? I need the format code itself because I need to use it in conjunction with a Text formula and a concatenate function. Just using the dialog box selection, as you mentioned, won't work in that case. Thanks...

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    _(* #,###_);_(* (#,###)

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 09:51, Yepper wrote:
    Hello, Yogi. Can you tell me what the "format code" (such as #,###)is that will enable me to have a negative number shown with parentheses and commas? I need the format code itself because I need to use it in conjunction with a Text formula and a concatenate function. Just using the dialog box selection, as you mentioned, won't work in that case. Thanks...
    Hi Yepper:
    for negative numbers to be represented in parenthesis, the Custom format component is
    ;(#,###)
    the semicolon says this custom format applies to negative numbers, the very first entry before the semicolon is reserved for positive numbers, and then the parenthesis says the negative number will be shown in parenthesis; and the ,### says that we are going to use the comma format -- meaning that thousands will be separated by commas.

    Hope This Helps!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A good way to tell what to put in the parens for your text formula:

    Click on a cell that has an unformatted number on it that is similar to the format the data will have,
    select format cells,
    go to the number tab,
    select custom,
    this will give you all the different formats, as well as showing what the number will look like once formatted.
    Hope that helps,
    -Corticus

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
  •