Using Replace Tool to remove specific text within a string (a comma separated list)

[edmund_belleza]

Afternoon,

I have a sample list of records and a column titled "DisneyWorld Parks Visited". See grid below. I want to remove "Magic Kingdom" from the data.

For Example, if Magic Kingdom is the only park listed, the field should be blank.

If another park is listed such as "Magic Kingdom,EPCOT,Hollywood Studios", it should return only "EPCOT,Hollywood Studios" (see third column)

I attempted to use the Replace tool to replace "Magic Kingdom" with "" (Blank). However, in the rows where Magic Kingdom is listed with another park(s), it leaves the comma at the beginning.

I then tried using one Replace Tool to first remove "Magic Kingdom," with "" to remove instances where Magic Kingdom is listed with another park(s), and a second to remove "Magic Kingdom" with "" for instances where only Magic Kingdom. However, I'm still getting the comma. (see fourth column)

1st Replace Tool: Replace([DisneyWorld Parks Visited], "Magic Kingdom,", "")

2nd Replace Too: Replace([DisneyWorld Parks Visited], "Magic Kingdom", "")

If Magic Kingdom is the only park listed, the field shows correctly as blank.

But if another park is listed such as "Magic Kingdom,EPCOT,Hollywood Studios", it returns return ",EPCOT,Hollywood Studios" (with the initial comma)

How can I remove that initial comma?

Record #	DisneyWorld Parks Visited	DisneyWorld Parks Visited (Excluding "Magic Kingdom") - Desired Format	DisneyWorld Parks Visited (Excluding "Magic Kingdom") - What I am currently getting.
1	Magic Kingdom
2	Magic Kingdom,EPCOT	EPCOT	,EPCOT
3	Magic Kingdom,EPCOT, Hollywood Studios	EPCOT, Hollywood Studios	,EPCOT, Hollywood Studios
4	Magic Kingdom,EPCOT, Hollywood Studios,Animal Kingdom	EPCOT, Hollywood Studios,Animal Kingdom	,EPCOT, Hollywood Studios,Animal Kingdom
5	EPCOT, Hollywood Studios	EPCOT, Hollywood Studios	EPCOT, Hollywood Studios
6	EPCOT,Animal Kingdom	EPCOT,Animal Kingdom	EPCOT,Animal Kingdom
7	Magic Kingdom,Animal Kingdom	Animal Kingdom	,Animal Kingdom
8	Animal Kingdom	Animal Kingdom	Animal Kingdom
9	Magic Kingdom,Hollywood Studios	Hollywood Studios	,Hollywood Studios
10	Animal Kingdom	Animal Kingdom	Animal Kingdom

[alexnajm · Accepted solution]

Add a TrimLeft(current formula, ",") to the Formula! This will remove any commas at the beginning - you could also just do Trim(current formula, ",") if you want to trim at the beginning and at the end

[Bren_Spill · Accepted solution]

@edmund_belleza - you could also put this in a formula tool: Replace(Replace([DisneyWorld Parks Visited],"Magic Kingdom,",""),"Magic Kingdom","")

[edmund_belleza]

Thank you! I wasn't aware of the trim formula!

[edmund_belleza]

Thank you! I didn't realize I could combine Replace Tool formulas like this. 

[Bren_Spill]

Yes, it's very useful! If you could also mark this as a solution, I'd be grateful (you can mark multiple as solution). Thanks!