Share via

calculated column to remove & and space "& "

Question

Thursday, August 15, 2013 9:53 PM

I have a department column in which some of the departments have a "&" in them and I 'd like to remove the & and following space with a calculated column.

example Customer Care & Service to be Customer Care Service

can anyone helpl?

All replies (5)

Friday, August 16, 2013 6:58 PM âś…Answered

This works for me (using the Title field, replace that with your field):

=IF(ISERR(SEARCH("&",Title,1)),Title,REPLACE(Title,SEARCH("&",Title,1),2,""))

Use the ISERR to see if the & is in the string. If there is an error, just return the field.

Otherwise, do your replace, using the search to find the position. In this case, replace 2 characers with null string.

Robin

Thursday, August 15, 2013 11:26 PM

Hi! 

As per the article:

Calculated Field Formulas:http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx

Follow this :

Remove characters from text

To remove characters from text, use the LEN, LEFT, and RIGHT functions.

Column1

Formula

Description (possible result)

Vitamin A

=LEFT([Column1],LEN([Column1])-2)

Returns 7 (9-2) characters, starting from left (Vitamin)

Vitamin B1

=RIGHT([Column1], LEN([Column1])-8)

Returns 2 (10-8) characters, starting from right (B1)

And then combine them.

Hope this helps!

Friday, August 16, 2013 3:30 PM

unfortunately that is only one of the 5 departments that has and "&" so the lengths are different depending on department I was fiddling around with the REPLACE function but haven't been having much success

Friday, August 16, 2013 9:32 PM

you are a STAR!! thanks so much!!!!!  that totally worked.... and thank you for explaining what each component meant.

Monday, June 13, 2016 3:57 PM

What if there are multiple '&' ?