How to combine two values with different formats in an Excel cell

Question

I'm trying to get the following in a single cell:

120.9 kWh in 7

This is a shorthand for a total amount of energy delivered to an EV in a total number of charging sessions during one month. The energy is calculated using SUM and the sessions using COUNTA.

At the moment, I have the energy cell formatted using 0.0" kWh", and I want to enhance it as above by specifying the energy value format in the formula.

To specify the number format for the energy value only, I tried using the TEXT function like this:

=TEXT(120.9, "0.0 kWh")

but Excel gave a #VALUE! error.

By experimentation, TEXT(120.9, "0.0 kW") works, but adding the "h" kills it.

I changed the cell format from 0.0" kWh" to General but that didn't help. When I get a solution, I plan to extend it as:

=TEXT(SUM(range), "0.0 kWh")&" in "&TEXT(COUNTA(range), "0")

Is there a solution?

Answer 1

To combine two values with different formats in a single Excel cell, you can use the TEXT function to format each part of the string separately. However, the issue you're facing with the TEXT function and the "kWh" format is likely due to the way Excel interprets the text string.

Here’s how you can achieve your desired output:

1. Format the energy value correctly: Use the TEXT function to format the energy value as you intended. You can use the formula:

      =TEXT(SUM(range), "0.0") & " kWh in " & TEXT(COUNTA(range), "0")
   

   This will format the sum of your energy values to one decimal place followed by " kWh" and concatenate it with the count of your sessions.
2. Final formula: Assuming you have your energy values in a range, your final formula would look something like this:

      =TEXT(SUM(A1:A10), "0.0") & " kWh in " & TEXT(COUNTA(B1:B10), "0")
   

   Replace A1:A10 and B1:B10 with your actual ranges.

This approach should work without giving you a #VALUE! error, as it separates the formatting of the energy value from the text string that follows it.

If you encounter any issues, ensure that the ranges you are summing and counting contain the appropriate data types (numbers for SUM and non-empty cells for COUNTA).

Answer 2

TEXT(120.9, "0.0 kW") works, but adding the "h" kills it.

Hi. I am not A.I. One way that tells Excel that "h" is a letter and not stand for "hour" might be:

=TEXT(120.9, "0.0 \k\W\h")