Extracting words from a cell and copy/move to new cell.

[wackykat80]

(Example 1) PULL MET.76MM (8/32) BRUSHED NICKEL - G3
color only would show in D3



(Example 2) HOOK MET.16MM (WOOD SCREW) CHROME - G4
color only would show in D4


(Example 3) PULL MET.16MM (M4) CHROME & BLACK - same thing

From these three examples I needed a formula to extract specific words from a cell and copy to another. Please help....I have tried doing the steps in the excel help but I don't get it.

 

[Gerald Higgins]

You need to identify some consistent pattern for identifying the colours.

In the three examples you posted, it LOOKS as if you can do this by finding a ")" character, and taking everything after that.

For example

=mid(G3,find(")",G3,1)+2,250)

should do it.

BUT, this won't work if some of your data doesn't have a ")" character, or doesn't have it immediately before the colour.

 

[wackykat80]

MUAH I love you...perfect! Thank u so much! I love this site!!

 

[machopicho]

(Example 1) PULL MET.76MM (8/32) BRUSHED NICKEL - G3
color only would show in D3

(Example 2) HOOK MET.16MM (WOOD SCREW) CHROME - G4
color only would show in D4

(Example 3) PULL MET.16MM (M4) CHROME & BLACK - same thing

From these three examples I needed a formula to extract specific words from a cell and copy to another. Please help....I have tried doing the steps in the excel help but I don't get it.

Another perzonalized Function by me:

Function COLORTEXT(rng As Range, ind As Integer) As String
Select Case ind
Case Is = 1
RGBcolor = RGB(255, 0, 0)   'Red
Case Is = 2
RGBcolor = RGB(0, 255, 0)   'Green
Case Is = 3
RGBcolor = RGB(0, 0, 255)   'Blue
Case Is = 4
RGBcolor = RGB(255, 255, 0) 'Yellow
Case Is = 5
RGBcolor = RGB(255, 0, 255) 'Magenta
Case Is = 6
RGBcolor = RGB(0, 255, 255) 'Cyan
End Select
n = Len(rng)
For i = 1 To n
If rng.Characters(i, 1).Font.Color = RGBcolor Then
COLORTEXT = COLORTEXT & Mid(rng, i, 1)
End If
Next
End Function

In based on the color of your text you can choose it through ind parameter of the function.
Enjoy!

 

[villy]

=Right(G3,Len(G3)-Search(")",G3)-1)