Remove specific word from cell

[tr2usa]

Sculpture,Zeus,Black,Ceramic 18x10x34"h

How can I pull "Black" from this description? I have 300 items and
colors needed to be removed. Thank you fro the help.

 

[Don Guillett]

one way
=SUBSTITUTE(B5,"Black,","")

 

[Ron de Bruin]

Or use Edit > Replace with the range selected

 

[tr2usa]

Sorry my question was wrong. Description is in B2, I want to pull
"BLACK" to C2 under Color header.

 

[Biff]

Hi!

One way:

If you know what all the possible colors are, make a list of those
somewhere, assume:

J1 = red
J2 = green
J3 = black
J4 = white
J5 = blue

B2 = Sculpture,Zeus,Black,Ceramic 18x10x34"h

C2 = formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(J1:J5,MATCH(TRUE,ISNUMBER(SEARCH(J1:J5,B2)),0))

Biff

 

[Don]

Another way to might be to create a list of colors as Biff suggested and
name the list "colors". Then run this marco to "pull the color out of column
B and put it in column C:


Sub movecolor()
Range("b2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Set myrange = Selection
Set colorlist = Range("colors")
For Each rrr In myrange
For Each ccc In colorlist
rrr.Select
Set a = Selection.Find(What:=ccc)
If Not a Is Nothing Then
ActiveCell = Replace(rrr, (ccc & ","), "")
rrr.Offset(0, 1) = ccc
Else
End If
Next ccc
Next rrr
End Sub

Don Pistulka

 

[tr2usa]

Thank you for your help. Solved my problem. I will try Don's solution
to learn.

 

[Don]

If you are going to learn from my code, I thought I might clean it up:



Sub movecolor()
Range("b2").Select
Set myrange = Range(ActiveCell, ActiveCell.End(xlDown))
Set colorlist = Range("colors")
For Each rrr In myrange
For Each ccc In colorlist
Set a = rrr.Find(What:=ccc)
If Not a Is Nothing Then
rrr.Value = Replace(rrr, (ccc & ","), "")
rrr.Offset(0, 1) = ccc
Else
End If
Next ccc
Next rrr
End Sub



Don Pistulka