Solved

Use cell value to nearest whole number in concatenate function

Posted on 2011-03-05
5
313 Views
Last Modified: 2012-08-13
I want to concatenate including using a cell that has a numberwith several decimal places. It is formatted to display as to the nearest whole number.   In the concatenated return I also want it to be to the nearest whole number, but it is of course displaying all it's decimal places.  How can I achieve this please?
0
Comment
Question by:robinbernett
5 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35043873
Am not sure I got you completely but I will try:
Put in C1:
=VALUE(CONCATENATE(A1,B1))
And make sure to format C1 as Number with 0 Decimal places.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 250 total points
ID: 35043950
If B1 is the decimal number, this perhaps?

=A1 & ROUND(B1,0)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 35043970
You can also use TEXT function like

=A1&" "&TEXT(B1,0)

or FIXED function to set the number of decimal places

=A1&" "&FIXED(B1,0)

regards, barry

0
 

Author Comment

by:robinbernett
ID: 35044137
Thanks all.

I couldn't make the VALUE work, but ROUND, TEXT, and FIXED did - so problem solved, and with choice!
Much appreciated - i've often wondered.
0
 

Author Closing Comment

by:robinbernett
ID: 35044146
A wizard, and sage and a genius to my rescue!  Very grateful as ever.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question