?
Solved

Use cell value to nearest whole number in concatenate function

Posted on 2011-03-05
5
Medium Priority
?
326 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 1000 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 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

593 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