Concatenate text and currency issue

PeterR

I am trying to concatenate or JOIN a column with currency and standard text - I can do this, however it doesn't show zeros on the currency, for example; instead of £2.00 I get £2

Can anyone help as to how I can get the formula to exactly replicate the currency value. Obviously adding a zero to every result isn't an option as I don't want to have £1.570 for example.

John Jonassen

Hi @PeterR

I think this might serve you:

="Was £" + IFERROR(IF((LEN(MID([Std Sell Price in VAT]@row, FIND(".", [Std Sell Price in VAT]@row), 3)) - 1) = 2, IFERROR(LEFT([Std Sell Price in VAT]@row, FIND(".", [Std Sell Price in VAT]@row) - 1), [Std Sell Price in VAT]@row) + MID([Std Sell Price in VAT]@row, FIND(".", [Std Sell Price in VAT]@row), 3), IF((LEN(MID([Std Sell Price in VAT]@row, FIND(".", [Std Sell Price in VAT]@row), 3)) - 1) = 1, IFERROR(LEFT([Std Sell Price in VAT]@row, FIND(".", [Std Sell Price in VAT]@row) - 1), [Std Sell Price in VAT]@row) + MID([Std Sell Price in VAT]@row, FIND(".", [Std Sell Price in VAT]@row), 3) + 0, "")), [Std Sell Price in VAT]@row + ".00")

PeterR

That gives me unparseable unfortunately.