Cutting Up Text by Commas

[nirvehex]

Hi,

I have a string of text in one cell, BW11 - "Color Change, Shape Same, Freq Decrease, Material Same, Game Same"

This phrase can alter based on other factors.

Essentially, what I'm trying to do is any where there is a comma, look back and see the first word. If it says same, do not copy, if it says decrease or increase or reduction, or any other word besides same it would copy it.

So in this case in BX11 I would want it to read "Color Change, Freq Decrease". So anything that is not changing or increasing or decreasing would be left out.

Is there a way to do this with mid, or left or substitute functions?

Thanks!

 

[WarPigl3t]

I don't know about how to do this with built in excel functions. You can create your own function.

Function myFunction(cell)
MySplit = split(cell, ", ")
For each i in mySplit(i)
mySplitAgain = spLit(mySplit(i)," ")
If lcase(mySplitAgain(1)) <>"same" then
Output = output & ", "&mySplit(i)
END if
i = i + 1
Next
myFunction = trim(mid(output,2,Len(output)-2))
END function

What this is saying is split the cell value by a comma and space delimiter.
Then split each split value again by a space. Read the second split value and if it doesn't equal "same", then add it to the output string.
Just type =myFunction(BW11) in whatever cell you want. There may be some syntax errors in my code that you will have to correct because I did this from memory and didn't test it.