Extracting numbers from a single based on font color

[PhantomJoe]

Hoping I can find some suggestions to help make a process easier for extracting numbers from a single cell. This is a process that I've just inherited and am looking to automate what I can.

Here's an example of how the data is coming over. What I'd like to do is extract only those numbers that have a font color other than black. In the case below I'd just want the numbers that are either red or purple in color. The numbers themselves will follow the same format as in my example (consisting of 7 numbers).[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 277"]
<tbody>[TR]
[TD="class: xl64, width: 277"]FT RSC: 1742652, 1742654, 1753552, 1743830, 1743831

PT RSC: 1753891, 1755233, 1742650, 1731819, 1731204, 1744316, 1751780, 1746235, 1753471, 1750298, 1748932, 1751501, 1742646, 1746235, 1744316, 1750298,1754930, 1756560, 1754304, 1755246, 1757191, 1750296, 1753483, 1753486, 1757195, 1754932, 1743822, 1743829, 1759831, 1759834, 1759844[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The current process is a completely a manual effort of cutting and pasting which I would like to avoid if at all possible.

Any suggestions?

 

[PhantomJoe]

Hoping I can find some suggestions to help make a process easier for extracting numbers from a single cell. This is a process that I've just inherited and am looking to automate what I can.

Here's an example of how the data is coming over. What I'd like to do is extract only those numbers that have a font color other than black. In the case below I'd just want the numbers that are either red or purple in color. The numbers themselves will follow the same format as in my example (consisting of 7 numbers).[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 277"]
<tbody>[TR]
[TD="class: xl64, width: 277"]FT RSC: 1742652, 1742654, 1753552, 1743830, 1743831

PT RSC: 1753891, 1755233, 1742650, 1731819, 1731204, 1744316, 1751780, 1746235, 1753471, 1750298, 1748932, 1751501, 1742646, 1746235, 1744316, 1750298,1754930, 1756560, 1754304, 1755246, 1757191, 1750296, 1753483, 1753486, 1757195, 1754932, 1743822, 1743829, 1759831, 1759834, 1759844[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The current process is a completely a manual effort of cutting and pasting which I would like to avoid if at all possible.

Any suggestions?

Also, is there a way to extract those numbers with a semicolon separating each 7 digit number?

 

[Rick Rothstein]

Here is a UDF (user defined function) that will extract the colored numbers (the output is not colored though as a formula cannot do that). Also note that the delimiter is a single semi-colon because that is what you asked for. If, however, you meant a semi-colon followed by a space, then change the part of my code highlighted in red to "; " instead (in other words, just put a space after the semi-colon).

Function ColoredNumbers(Rng As Range) As String
  Dim X As Long
  ColoredNumbers = Rng.Value
  For X = 1 To Len(Rng.Value)
    If Rng.Characters(X, 1).Font.Color = 0 Or Mid(ColoredNumbers, X, 1) Like "[!0-9]" Then Mid(ColoredNumbers, X, 1) = " "
  Next
  ColoredNumbers = Replace(Application.Trim(ColoredNumbers), " ", [B][COLOR="#FF0000"]";"[/COLOR][/B])
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ColoredNumbers just like it was a built-in Excel function. For example,

=ColoredNumbers(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[PhantomJoe]

Here is a UDF (user defined function) that will extract the colored numbers (the output is not colored though as a formula cannot do that). Also note that the delimiter is a single semi-colon because that is what you asked for. If, however, you meant a semi-colon followed by a space, then change the part of my code highlighted in red to "; " instead (in other words, just put a space after the semi-colon).

Function ColoredNumbers(Rng As Range) As String
  Dim X As Long
  ColoredNumbers = Rng.Value
  For X = 1 To Len(Rng.Value)
    If Rng.Characters(X, 1).Font.Color = 0 Or Mid(ColoredNumbers, X, 1) Like "[!0-9]" Then Mid(ColoredNumbers, X, 1) = " "
  Next
  ColoredNumbers = Replace(Application.Trim(ColoredNumbers), " ", [B][COLOR=#FF0000]";"[/COLOR][/B])
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ColoredNumbers just like it was a built-in Excel function. For example,

=ColoredNumbers(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

This worked perfectly. Thank you very much for your help and expertise.