topic Re: Remove some strings from a field in Water Cooler

Remove some strings from a field

geogou1973 — Mon, 30 Dec 2019 19:44:44 GMT

Hello,

I have a field that is the document number of the invoice.

This field will have some strings like . or / or - or etc

how can i remove from all data these strings that have come from false data entry.

I tried with the the following command

IF(FINDONEOF(DOC_PURCHASES,'.'),REPLACE(DOC_PURCHASES,'.',''),
IF(FINDONEOF(DOC_PURCHASES,'/'),REPLACE(DOC_PURCHASES,'/',''),
IF(FINDONEOF(DOC_PURCHASES,'-'),REPLACE(DOC_PURCHASES,'-',''),
IF(FINDONEOF(DOC_PURCHASES,','),REPLACE(DOC_PURCHASES,',',''),
DOC_PURCHASES))))

but in some values does not work and i don't know why.

Have anyone an idea how can i do this.

Thank you in advance.

Re: Remove some strings from a field

Frank_Hartmann — Mon, 30 Dec 2019 21:04:08 GMT

what if you try using purgecharacter function?

purgechar(DOC_PURCHASES,'.-/')

Re: Remove some strings from a field

Kushal_Chawda — Mon, 30 Dec 2019 21:54:48 GMT

As @Frank_Hartmann suggested you can either use purgechar function in which you can write as many junk characters that you want to remove. If single quotes itself is one of the junk character then you can use it like below

=PurgeChar(DOC_PURCHASES,chr(39)&'-.,')

If you field always going to have number value then you can use keepchar function which will automatically remove all other characters from field

Keepchar(DOC_PURCHASES,'0123456789')