ExcelBanter - Extracting just the color words

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Extracting just the color words (https://www.excelbanter.com/excel-worksheet-functions/9390-extracting-just-color-words.html)

Extracting just the color words

Hi, I have table with A,B,C and D colums and text in the rows.
Some of the words in the text are with bold and different color letter.
I need to extract those words.
How can I do this?

I'm using Excel 2003

Thanks in advance,

Mariela

Mariela,

You would need to use a User-Defined-Function. See the definition below.
Copy and paste it into a codemodule in your workbook, then use it like

=ColorWord(A1,3)

to extract the red letters from cell A1.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
ColorWord = ColorWord & Mid(myCell.Text, i, 1)
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Hi, I have table with A,B,C and D colums and text in the rows.
Some of the words in the text are with bold and different color letter.
I need to extract those words.
How can I do this?

I'm using Excel 2003

Thanks in advance,

Mariela

Hi Bernie,

Thanks for your help. It's really appriciate.
I have one more question. What should I put in iColor field - number 3 or
something else?

Mariela

"Bernie Deitrick" wrote:

Mariela,

You would need to use a User-Defined-Function. See the definition below.
Copy and paste it into a codemodule in your workbook, then use it like

=ColorWord(A1,3)

to extract the red letters from cell A1.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
ColorWord = ColorWord & Mid(myCell.Text, i, 1)
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Hi, I have table with A,B,C and D colums and text in the rows.
Some of the words in the text are with bold and different color letter.
I need to extract those words.
How can I do this?

I'm using Excel 2003

Thanks in advance,

Mariela

Ok, I understand how formula is working. And I saw I wasn't clear enough of
what I want it to get as resault. So I have table with text in the rows.
Words are in black and just some of them have red letter. What I need to do
is to extract the whole word that contain red letter, not just the letter.

it is something like that
A B C D
1 bl<ue blue blue blue
2 blue blue blue blu<e
3 blu<e b<lue bl<ue blu<e
4 blue blue blue blue

< - red letter

Mariela
"Bernie Deitrick" wrote:

Mariela,

You would need to use a User-Defined-Function. See the definition below.
Copy and paste it into a codemodule in your workbook, then use it like

=ColorWord(A1,3)

to extract the red letters from cell A1.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
ColorWord = ColorWord & Mid(myCell.Text, i, 1)
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Hi, I have table with A,B,C and D colums and text in the rows.
Some of the words in the text are with bold and different color letter.
I need to extract those words.
How can I do this?

I'm using Excel 2003

Thanks in advance,

Mariela

Mariela,

Use the modified version below.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
Dim j As Integer
Dim Start As Integer
Dim myEnd As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
For j = i To 1 Step -1
If Mid(myCell.Text, j, 1) = " " Then
Start = j + 1
GoTo FindEnd
End If
Next j
Start = 1
FindEnd:
For j = i To Len(myCell.Text)
If Mid(myCell.Text, j, 1) = " " Then
myEnd = j - 1
GoTo AllFound
End If
Next j
myEnd = Len(myCell.Text)
AllFound:
ColorWord = Mid(myCell.Text, Start, myEnd - Start + 1)
Exit Function
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Ok, I understand how formula is working. And I saw I wasn't clear enough
of
what I want it to get as resault. So I have table with text in the rows.
Words are in black and just some of them have red letter. What I need to
do
is to extract the whole word that contain red letter, not just the letter.

it is something like that
A B C D
1 bl<ue blue blue blue
2 blue blue blue blu<e
3 blu<e b<lue bl<ue blu<e
4 blue blue blue blue

< - red letter

Mariela
"Bernie Deitrick" wrote:

Mariela,

You would need to use a User-Defined-Function. See the definition
below.
Copy and paste it into a codemodule in your workbook, then use it like

=ColorWord(A1,3)

to extract the red letters from cell A1.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
ColorWord = ColorWord & Mid(myCell.Text, i, 1)
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Hi, I have table with A,B,C and D colums and text in the rows.
Some of the words in the text are with bold and different color
letter.
I need to extract those words.
How can I do this?

I'm using Excel 2003

Thanks in advance,

Mariela

Thanks a Lot Bernie!!!!

It's works!!

Mariela

"Bernie Deitrick" wrote:

Mariela,

Use the modified version below.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
Dim j As Integer
Dim Start As Integer
Dim myEnd As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
For j = i To 1 Step -1
If Mid(myCell.Text, j, 1) = " " Then
Start = j + 1
GoTo FindEnd
End If
Next j
Start = 1
FindEnd:
For j = i To Len(myCell.Text)
If Mid(myCell.Text, j, 1) = " " Then
myEnd = j - 1
GoTo AllFound
End If
Next j
myEnd = Len(myCell.Text)
AllFound:
ColorWord = Mid(myCell.Text, Start, myEnd - Start + 1)
Exit Function
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Ok, I understand how formula is working. And I saw I wasn't clear enough
of
what I want it to get as resault. So I have table with text in the rows.
Words are in black and just some of them have red letter. What I need to
do
is to extract the whole word that contain red letter, not just the letter.

it is something like that
A B C D
1 bl<ue blue blue blue
2 blue blue blue blu<e
3 blu<e b<lue bl<ue blu<e
4 blue blue blue blue

< - red letter

Mariela
"Bernie Deitrick" wrote:

Mariela,

You would need to use a User-Defined-Function. See the definition
below.
Copy and paste it into a codemodule in your workbook, then use it like

=ColorWord(A1,3)

to extract the red letters from cell A1.

HTH,
Bernie
MS Excel MVP

Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
ColorWord = ColorWord & Mid(myCell.Text, i, 1)
End If
End With
Next i
End With
End Function

"Mariela" wrote in message
...
Hi, I have table with A,B,C and D colums and text in the rows.
Some of the words in the text are with bold and different color
letter.
I need to extract those words.
How can I do this?

I'm using Excel 2003

Thanks in advance,

Mariela