select " ;"  then ok

Try solution provided by msa s  or try with IF condition.

IF FIND([Business Segment],";#") = 0

THEN [Business Segment]

ELSE LEFT([Business Segment],FIND([Business Segment],";#")-1)

END

REPLACE([String field], ";" , "")

that will put a null string in place of whatever is in the middle argument.

How many different special conditions are you looking to remove?  To remove multiple conditions you would nest them like this:

REPLACE(REPLACE([String field], ";" , ""), ";#8", "")

Give the limited list of examples you have, maybe you could just do

MID([String Field], 1, 4)

That will just grab the first 4 chars of your string.

Does your string of special characters always start with a semicolon?  If so, do a FIND() on the string and look for the semicolon.  If the result is 0, then it's not in there and you just use the string as is.  Else, do

MID([String field], 1, (FIND([String Field], ";")-1))

And if all else fails, there is the REGEX() function that lets you search and replace all sorts of things.  (It's a whole world unto itself, so let's leave that for last if you need to go there.)

I never saw that before either.

Thanks, MSA S !!

That condition you are using is wrong.you want to cover all scenario where ";#" is present and not just when it is present at the beginning.

It should be like following.

if FIND([Business Segment],";#") >=1 then LEFT([Business Segment],FIND([Business Segment],";#")-1)

else [Business Segment]

END

or you can do following.

if FIND([Business Segment],";#") > 0 then LEFT([Business Segment],FIND([Business Segment],";#")-1)

else [Business Segment]

END

Thankyou Joe  for the answer and detailed explanation