Text to columns

[Saikat755]

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,

Saikat.

[#2 · Accepted solution]

@Saikat755 

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

=SplitText(A1)

In C1

=SUBSTITUTE(A1,B1,"")

[#3]

Thanks @Subodh_Tiwari_sktneer This helps me solve my purpose.

Regards and Best,

Saikat.

[#4]

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