# Add currency symbol to formula

Topic Labels: Formulas
1493 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?

3 Replies 3
18 - Pluto

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

5 - Automation Enthusiast

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.

5 - Automation Enthusiast

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
)
))
``````