Text to columns

New Contributor

Hi All,

I am facing a problem in text to column transformation with the following data (I have 10k rows of similar pattern)


"Sky: Children of the Lightthatgamecompany"


I have the data in which the first part is in "Blue Color" and the second part which is in black. I want to separate both of them.


Any leads are appreciated.


Thanks and Best,


3 Replies
Best Response confirmed by Saikat755 (New Contributor)


You may create a User Defined Function to Split the Text based on font color.

Place the following code on a Standard Module like Module1 and then you can use this UDF on Worksheet just like a Regular Excel Function.


Function SplitText(Rng As Range) As String
Dim i       As Long
Dim strOut  As String
Dim str     As String
str = Rng.Value
For i = 1 To Len(str)
    If Rng.Characters(i, 1).Font.Color = RGB(0, 112, 192) Then
        strOut = strOut & Mid(str, i, 1)
    End If
Next i
SplitText = strOut
End Function


Assuming your string is in cell A1, then try this...


In B1



In C1



Split Text.jpg


Thanks @Subodh_Tiwari_sktneer This helps me solve my purpose.


Regards and Best,


You're welcome @Saikat755! Glad it worked as desired.