Identify different colour letters in a string

Having researched many of the Excel help sites, we have not been able to find a solution for splitting out text in the same cell where a colour changes, or a string is a particular colour within a string. A client required this solution, and after several hours of trialling different situations, we have created the example code below that should give you an idea of how to deal with a brief of this sort.

Our clients project needed to loop down a large list in 1 column of data that had been exported from a language website into Excel and where a colour of the text changed, move it across a cell.

Initially we didn’t think this was possible but after exhausting all avenues of research we have created a very short but particularly neat script to give an example of how this could work. Whilst we haven’t included the full script you should be able to understand the logic of the code and adapt for your own needs whether that be to split text where colours change in the same cell, identify locations in the text where it changes, or count the number of each coloured character.

Feel free to use or adapt the code for your own use or contact us for further assistance. As this is the only solution we had identified we’d be more than happy to help you build your own solution using our example below.


Sub Button1_Click()
lnth = Len(ActiveSheet.Range("C4"))
For ch = 1 To lnth
ActiveSheet.Range("C4").Select
If Selection.Characters(ch, 1).Font.Color = 255 Then
ActiveSheet.Range("D4") = Mid(ActiveSheet.Range("C4"), ch, 255)
End If
Next ch
End Sub






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *