Need Some Excel Formatting Help

Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum

Help Support Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.

jescholler

Well-Known Member
Joined
Feb 23, 2009
Messages
535
Reaction score
8
Location
Louisville
I'm trying to create a brewing spreadsheet. What I'm trying to do now is take the low and high gravity for a style and place it in a single cell with a hyphen between (e.g. 1.045-1.060). I have the 1.045 and the 1.060 stored in other cells, lets say A1 and A2 respectively.

Right now, I use the formula =A1&"-"&A2.

The problem is because of the formatting, it results in the cell displaying "1.045-1.06" (without the 0 after the 6).

It's not a huge deal, mainly looks, but there has to be a way for it to display like "1.045-1.060".
 
If you set the cell format to "text" rather than "general" or "number", you may be able to keep all the formatting the same.
 
Create a custom number format with 3 decimals and a hyphen. Look at the examples in the dialog box under phone numbers, Social Security numbers, and decimal numbers. It should be somewhat intuitive.

I'd give you the format myself, but I'm using Mac, and I don't have the MS Office suite (and I can't remember the exact characters to type).
 
The text formatting didn't work, and I'm not getting the custom formatting to work either.

For the custom format, I tried a couple of things, but I think what I want is "0.000-0.000". I think it's not working because of the concatenation in the formula.

I ended up getting rid of the decimal point, so now it looks better.
 
I'm not having luck removing the concatenation either and then adding it in the custom format. I tried it both when the original cell was 1060 and 1.060.

What mvdilts suggested did work though (with slight modification)! Here's what I ended up with.

=TEXT(A1,"0.000")&"-"&TEXT(A2,"0.000")

I needed to use "0.000" instead of "#.###" because # doesn't display extra zeros. The 0 pads the value with zeros to fill the format.

Thanks everyone! Now back to things that actually affect the functionality of my spreadsheet and not just looks.
 
I'm trying to create a brewing spreadsheet. What I'm trying to do now is take the low and high gravity for a style and place it in a single cell with a hyphen between (e.g. 1.045-1.060). I have the 1.045 and the 1.060 stored in other cells, lets say A1 and A2 respectively.

Right now, I use the formula =A1&"-"&A2.

The problem is because of the formatting, it results in the cell displaying "1.045-1.06" (without the 0 after the 6).

It's not a huge deal, mainly looks, but there has to be a way for it to display like "1.045-1.060".

In the cell with the numbers (i.e., A1 and A2), precede the number with an apostrophe ('). This will retain the following zeros. This trick has existed since the earliest version of Excel. I miss Lotus 1-2-3.
 
Back
Top