Formula with last symbols

[Jon_Wright]

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?

[TheTimeSavingCo]

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

[Jon_Wright]

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]

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