I have 3 columns in my table that contain an amount range e.g. 3500;4800
What I want to do is split that string out into Budget From & Budget To fields, like this:
I’ve managed to do the actual splitting how I want using this formula for the Budget From:
IF( FIND(";", {Budget range PER PERSON $}),
LEFT({Budget range PER PERSON $},
FIND(";", {Budget range PER PERSON $})-1
),
{Budget range PER PERSON $}
)
&
IF( FIND(";", {Budget range PER PERSON £}),
LEFT({Budget range PER PERSON £},
FIND(";", {Budget range PER PERSON £})-1
),
{Budget range PER PERSON £}
)
&
IF( FIND(";", {Budget range PER PERSON €}),
LEFT({Budget range PER PERSON €},
FIND(";", {Budget range PER PERSON €})-1
),
{Budget range PER PERSON €}
)
But what I want to be able to do is append the correct currency symbol, which I can’t work out how to do, so something like this:
IF( FIND(";", {Budget range PER PERSON $}),
LEFT({Budget range PER PERSON $},
FIND(";", {Budget range PER PERSON $})-1
),
& "£", {Budget range PER PERSON $}
)
&
IF( FIND(";", {Budget range PER PERSON £}),
LEFT({Budget range PER PERSON £},
FIND(";", {Budget range PER PERSON £})-1
),
& "$", {Budget range PER PERSON £}
)
&
IF( FIND(";", {Budget range PER PERSON €}),
LEFT({Budget range PER PERSON €},
FIND(";", {Budget range PER PERSON €})-1
),
& "€", {Budget range PER PERSON €}
)
Anyone any ideas?