+ Reply to Thread
Results 1 to 7 of 7

Rounding concatenated number

  1. #1
    Registered User
    Join Date
    08-25-2014
    Location
    New York, USA
    MS-Off Ver
    2010
    Posts
    2

    Rounding concatenated number

    I have the following values 774, 918, 1495 - I am trying to display them with text and multiplication:

    =CONCATENATE("14K: $",AJ3*1.9, " 18K: $",AJ67*1.9," PLAT: $",AJ93*1.9)

    The following is displayed: 14K: $1470.6 18K: $1744.2 PLAT: $2840.5

    I want to have the numbers to be rounded off, in other words, no decimals.

    How can I round them?

    Thank you

  2. #2
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Rounding concatenated number

    =concatenate("14k: $",roundup((aj3*1.9),0), " 18k: $",roundup((aj67*1.9),0)," plat: $",roundup((aj93*1.9)),0)

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Rounding concatenated number

    Try

    =CONCATENATE("14K: $",ROUND(AJ3*1.9,0), " 18K: $",ROUND(AJ67*1.9,0)," PLAT: $",ROUND(AJ93*1.9,0))

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Rounding concatenated number

    Here are two approaches:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    If you don't want commas in your numbers, then...
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Rounding concatenated number

    Quote Originally Posted by Jonmo1 View Post
    Try

    =CONCATENATE("14K: $",ROUND(AJ3*1.9,0), " 18K: $",ROUND(AJ67*1.9,0)," PLAT: $",ROUND(AJ93*1.9,0))
    Of course... ROUND is better than my ROUNDUP

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Rounding concatenated number

    Quote Originally Posted by James Keuning View Post
    Of course... ROUND is better than my ROUNDUP
    I wouldn't say 'better', just different.
    For all we know, the OP might actually want it rounded up. We don't know, because that wasn't specified...

  7. #7
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Rounding concatenated number

    ^ Fair enough; I wasn't paying attention to what I was doing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count if one number of a concatenated cell is greater than 0
    By te31 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-29-2013, 03:46 PM
  2. [SOLVED] Rounding the number produced by an evaluated expression to the nearest number in CF
    By orly_sm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2013, 09:16 AM
  3. vba to Match concatenated values in concatenated columns
    By bjurick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2012, 03:45 PM
  4. Number format in a concatenated formula
    By LstarS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-18-2007, 12:38 PM
  5. format a number concatenated with text
    By jmwismer in forum Excel General
    Replies: 2
    Last Post: 02-23-2006, 12:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1