Jul 18 2020 01:14 AM
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.
Jul 18 2020 02:31 AM
SolutionYou 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,"")
Jul 18 2020 11:42 AM
Jul 18 2020 02:31 AM
SolutionYou 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,"")