Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Add currency symbol to formula

Topic Labels: Formulas
2330 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jon_Wright
5 - Automation Enthusiast
5 - Automation Enthusiast

I have 3 columns in my table that contain an amount range e.g. 3500;4800
Screenshot 2022-09-22 at 18.26.19

What I want to do is split that string out into Budget From & Budget To fields, like this:
Screenshot 2022-09-22 at 18.28.23

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

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

Screenshot 2022-09-23 at 3.58.47 PM

Screenshot 2022-09-23 at 3.59.19 PM

Screenshot 2022-09-23 at 3.59.11 PM

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.

Jon_Wright
5 - Automation Enthusiast
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
)
))