Formatting field

[darren_dixon]

Hi

I have a field called serviceArea which displays with __b, __d, __f etc. instead of spaces (Attached below).

Rather than writing a long if statement I was wondering if there was some formatting which could be applied in the script to tidy this up?

Thanks,

Darren

1st__bTouch__bMobile

A__fD__baccount

A__fD__bLogin

Accidents__b__u__bMS__bAccess

Allpay

Anti__bVirus

Apex__bAsset__bManagement

Asbestos__bManagement

Asta__bPowerproject

Audio__bVisual

Autocad__fTeamwork__fPlotter

[bobdawes]

My guess is you're best bet is using a replace() string function.   The example from help:

replace('abccde','cc','xyz') returns 'abxyzde'

you'll want something along the lines of replace (serviceArea,'__b',' '), though you will probably want to embed several replaces to handle __d and __f and any other examples you have.

Using purge() function to simply remove underscores won't work, as you'll be left with the extra letters (d, f, etc.)

[markmccoid]

What is the final view of the data that you need?

Are you trying to replace those __b, etc or use them as delimiters and split the field into multiple fields?

[darren_dixon]

I'm looking to replace __b, __d, __f.

The replace(serviceArea,'__b',' ') is great but how would I do this for all three.

Replace(Service__bArea,'__b',' ') AS Test,

[nizamsha]

=PurgeChar(serviceArea,'-')  its in listbox

=replace('abc-cd-e','-','') it will work in text object

[tresesco · Accepted solution]

May be like this?

Replace(Replace(Replace(ServiceArea, '_b', ' '), '_d', ' '), '_f' , ' ')) as NewServiceArea

Not very dynamic, but this is what it comes in mind now.

[Anonymous]

use an If(

               serviceArea = '_b',

               Replace( ...),

               if(

                         serviceArea ='_c'

                         Replace(...),

                         Replace (..case for _d)

                    )

               ) as Test,