Remove word from string 1

[fileman1]

I have a string filled with words seperated by spaces.

TextString = "SATURN MARS JUPITER MOON"

If I wanted to remove a selected word, ie MARS and end up with TextString = "SATURN JUPITER MOON" how do I do it?

Can someone suggest/show how to do it.

 

[RobertIngles]

This string is in a table yes? I have done it by creating a query that results in only the column of fields I want to remove the text from and doing a simple find and replace with from the edit menu.

ie. search for SATURN MARS JUPITER MOON Replace with SATURN JUPITER MOON. You can also search for Mars and replace with nothing (blank).

 

[fileman1]

Thanks Robert, but no, the text string is not in a table.

 

[PHV]

TextString = "SATURN MARS JUPITER MOON"
TextString = Replace(Replace(TextString, " MARS", ""), "MARS ", "")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thank you, that's very slick. My attempt looked crazy using split etc.

I did try to modify your code in case I ended up sometime in the future with separators ie commas etc, but could not get it to work. Can I trouble you with that one?

 

[PHV]

I did try to modify your code
So, what did you try ?

 

[fileman1]

I first added commas
TextString = "SATURN,MARS,JUPITER,MOON"

Tried
TextString = Replace(Replace(TextString, " MARS,", ""), "MARS, ", "")
TextString = Replace(Replace(TextString, " MARS,", ","), "MARS, ", ",")
TextString = Replace(Replace(TextString, " MARS,", ""), "MARS, ", "")

e.t.c Just thinking I had to incorporate a comma somewhere but nothing worked so I put it to one side.
I thought the syntax was in 2 parts, what to look for and what to replace it with, but did not know why MARS appears in both parts.

 

[PHV]

You don't have space around the commas in your string, so:
TextString = Replace(Replace(TextString, "MARS,", ""), ",MARS", "")

I use 2 Replace to cover the cases where MARS is at the beginning or the end of the string.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thanks for your valuable help. I was so near but too too far away.

 

[fileman1]

I thought that was the end, however what happens if the word to remove is in a variable which would be most likely.
Some of my attempts

LP = "MOON"

textstring = "SATURN,MARS,JUPITER,MOON"
textstring = Replace(Replace(textstring, "'LP',", ""), ",LP", "")
textstring = "SATURN,MARS,JUPITER,MOON"
textstring = Replace(Replace(textstring, "'LP',", ""), ",'LP'", "")
textstring = "SATURN,MARS,JUPITER,MOON"
textstring = Replace(Replace(textstring, "'LP',", ""), ",'LP", "'")
textstring = "SATURN,MARS,JUPITER,MOON"
textstring = Replace(Replace(textstring, "'LP,'", ""), "',LP'", ""

 

[PHV]

textstring = Replace(Replace(textstring, LP & ",", ""), "," & LP, "")

Don't confuse variable and literal.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thanks, I would never have got there!