Formatting strings

  • Thread starter Thread starter strawberry
  • Start date Start date
S

strawberry

I have a column with the following formula e.g.

=CONCATENATE(B31,C31)

The values in column B are either text or blank. The values in C are
numbers.

The output presently look like this
ColB ColC Result
FF0 5 FF05
5.07 5.07
5.08 5.08
5.09 5.09
5.1 5.1 <-- Note in Excel, ColC actually displays as "5.10" - but
it pastes like this.
5.11 5.11
5.12 5.12

I'd like the Result column to look like this (I don't mind if changes
need to be made to the other columns):
ColB ColC Result
FF0 5 FF05
5.07 5.07
5.08 5.08
5.09 5.09
5.1 5.10 <-- all results formatted to 2 decimal places regardless
of zeros.
5.11 5.11
5.12 5.12
5.11 5.11
5.12 5.12

I've been tearing my hair out playing with custom formats trying to
make this work. Any help greatly appreciated.
 
Try this:

=B31 & TEXT(C31,"0.00")

If you want a space between your text and the number (but no space if
column B is empty), you can do this:

=TRIM(B31 & " " & TEXT(C31,"0.00"))

Note that & does the same as CONCATENATE, but is easier to type.

Hope this helps.

Pete
 
Try this:

=B31 & TEXT(C31,"0.00")

If you want a space between your text and the number (but no space if
column B is empty), you can do this:

=TRIM(B31 & " " & TEXT(C31,"0.00"))

Note that & does the same as CONCATENATE, but is easier to type.

Hope this helps.

Pete
Click to expand...

Pete_UK

Thanks - much better!

It now adds ".00" to the FF0# string - but I think I can live with
that. ;-)

Thanks again.
 
Sorry, I didn't realise that was part of your data. You can overcome
it like this:

=B31 & TEXT(C31,IF(B31="","0.00","0"))

Hope this helps.

Pete
 
Back
Top