Formating numbers so they show as rand values eg 123 456 789.00

[tonywatsonhelp]

Hi Everyone,
now this sounds simple,
all i want is to format a value to show as the money value Rand, now rand shows as "R" then the money value but has a spance after every 3rd number for example
R1 000 000.00
R11 000 000.00
R111 000 000.00
R1 111 000 000.00
R11 111 000 000.00
R111 111 000 000.00
R2 111 111 000 000.00
R22 111 111 000 000.00
R222 111 111 000 000.00
R3 222 111 111 000 000.00
The problem i'm having is either the number of times it does a space runs out, for example
="The clients need the amount of "& TEXT('Data Inputs'!X8,"R### ###.00")) gives me just one space (eg R2211111000 000.000)
or i add more and to many spaces like this ="The clients need the amount of "& TEXT('Data Inputs'!X8,"R### ### ### ### ###.00") (eg R 111 111 000 000.00) the gap between R and first number is not acceptable

how can i do it so i get the exact look above?
Data Inputs'!X8 is the number and is just input as a number like 100000

please help if you can its driving me crazy

thanks

Tony

 

[Bo_Ry]

Try
="R"&TRIM(TEXT( 'Data Inputs'!X8 ,"### ### ### ### ###.00"))

 

[tonywatsonhelp]

Brilliant, thank you