Skip to main content

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?

Hm, what if you just converted your result into a number and used the Currency formatting?





Hm, what if you just converted your result into a number and used the Currency formatting?





Thanks for the suggestion, ideally not how I want to do it as the currency symbol could be £, $ or €. So doing it that way would mean I need six columns.


I may however end up going down this route and hide the six columns and then create a seventh with a single result in.


Quick update. I worked it out in the end. My formula for the Budget From column looks like this:


IF( FIND(";", {Budget range PER PERSON $}),
CONCATENATE("$", LEFT({Budget range PER PERSON $},
FIND(";", {Budget range PER PERSON $})-1
))
)
&
IF( FIND(";", {Budget range PER PERSON £}),
CONCATENATE("£", LEFT({Budget range PER PERSON £},
FIND(";", {Budget range PER PERSON £})-1
))
)
&
IF( FIND(";", {Budget range PER PERSON €}),
CONCATENATE("€", LEFT({Budget range PER PERSON €},
FIND(";", {Budget range PER PERSON €})-1
))
)


My Budget To column formula looks like this:


IF( FIND(";", {Budget range PER PERSON $}),
CONCATENATE("$", LEFT(
RIGHT({Budget range PER PERSON $},
LEN({Budget range PER PERSON $}) - LEN({Budget From})
),
FIND( ";",
RIGHT({Budget range PER PERSON $},
LEN({Budget range PER PERSON $}) - LEN({Budget From})
) & ";"
)-1
)
))
&
IF( FIND(";", {Budget range PER PERSON £}),
CONCATENATE("£", LEFT(
RIGHT({Budget range PER PERSON £},
LEN({Budget range PER PERSON £}) - LEN({Budget From})
),
FIND( ";",
RIGHT({Budget range PER PERSON £},
LEN({Budget range PER PERSON £}) - LEN({Budget From})
) & ";"
)-1
)
))
&
IF( FIND(";", {Budget range PER PERSON €}),
CONCATENATE("€", LEFT(
RIGHT({Budget range PER PERSON €},
LEN({Budget range PER PERSON €}) - LEN({Budget From})
),
FIND( ";",
RIGHT({Budget range PER PERSON €},
LEN({Budget range PER PERSON €}) - LEN({Budget From})
) & ";"
)-1
)
))

Reply