Search code examples
excelexcel-formula

Excel cannot format numbers in result string

It seemed to be an easy work, but I can't get it done:

Given:

I4: 0.32

E4: 12

=Round(I4*2;2) & "/pc  //  " & Round(I4*2*E4;2) & "/unit"

gives the result string "0.64 /pc // 7.68 /unit"

I want to format the result into

"€ 0.64 /pc  //  € 7.68 /unit"

I tried to use TEXT() but in any worksheet TEXT() does not work!

Simple: =TEXT(I4;"00") even gives #NAME

Same as =TEXT(I4;"€ #.##0,00") which should give "€ 0,32".

So =TEXT(Round(I4*2;2);"€ #.##0,00 /pc") & " // " & TEXT(Round(I4*2*E4;2); & ""€ #.##0,00 / unit") does not work and gives #VALUE.

Thanks your help.

Solution

  • TEXT function - Microsoft Support

    We could use full format code (specifying postive;negative;zero and suffix), also make use of automatic rounding:

    =TEXT(I4*2, "[$€-x-euro2] #,##0.00 "" /pc"";;;") & " // "
& TEXT(I4*2*E4,"[$€-x-euro2] #,##0.00 "" /unit"";;;")

    But defining the format codes in Name Manager will make them easy use and maintain.

    Formula with result

    Please change , etc. to match your regional settings.