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


Terms & Conditions