How to remove spaces & hypens in a field without using purgechar

[Jesh19]

Hi All,

I am having a requirement where my department field values are as below:

123456789

123456789-ABC

123456789 - ABC

123456789 - A & B - B - C

A & B - B - C (123456789)

Other - 123456789

Other(123456789)

I need output like:

123456789

ABC

ABC

A & B - B - C

A & B - B - C

Other

Other

Please help me in solving the issue.

Thanks & Regards,

Jeshwanth B

[shraddha_g]

Try

subfield(Filedname,'-',1)

[neha_shirsath]

Hello Jeshwanath,

Try this logic-

=if (purgechar(test,'0123456789')='',test,

if(isText(test),SubField(test,'-',2),test))

Thanks,

Neha

[Jesh19]

Got the solution.

=if(purgechar(Department,'0123456789')='',Department,

Trim(Upper(If(Right(Trim(PurgeChar(If(Num(Left(Department,1)),Mid(Department,13),If(Right(Department,1)=')',Left(Department,Len(Department)-12))),'()')),1)='-',

Left(Trim(PurgeChar(If(Num(Left(Department,1)),Mid(Department,13),If(Right(Department,1)=')',Left(Department,Len(Department)-12))),'()')),

Len(Trim(PurgeChar(If(Num(Left(Department,1)),Mid(Department,13),If(Right(Department,1)=')',Left(Department,Len(Department)-12))),'()')))-1),

Trim(PurgeChar(If(Num(Left(Department,1)),Mid(Department,13),If(Right(Department,1)=')',Left(Department,Len(Department)-12))),'()'))))) )

Regards,

Jeshwanth B

[jonathandienst]

Try this:

=If(IsNum(department),

  department,

  Replace(Replace(KeepChar(department, '0123456789'), ''), '()', '')

)

or

=If(IsNum(department),

  department,

  Replace(PurgeChar(department, '0123456789'), '()', '')

)

Replace department with the correct field name. If the value is numeric, do nothing, otherwise purge the numerals and any remaining parentheses.