Separate colored text from cell, in different cells on another sheet

[Visor]

Greetings, friends of the forum, after dealing and looking for solutions, I am presenting this topic to you. I have a cell with text where a different color letter color has been placed in certain parts. I would like to use macros to make each color text go to different cells as I have put it on the sheet2.

Try doing with macro recorder but it saves the text of the cell itself and it will not always be the same text. So when I bring another text or I do the differentiation by color and then I would apply the macro to separate into cells on the other sheet.

I upload a file to be seen

I appreciate your contributions in advance

 

[NARAYANK991]

Hi ,

How many colours will you be using , and will these colours always be the same ?

Narayan

 

[Hui]

Try this code:

Sub SeparateTextbyColor()

Dim c As Range
Dim cellcount As Integer
Dim i As Long, start As Long


cellcount = 4
For Each c In Selection
  start = 1
  For i = 2 To Len(c)
      If c.Characters(start:=i, Length:=1).Font.Color <> c.Characters(start:=i - 1, Length:=1).Font.Color Then

        Hoja2.Cells(cellcount, 2) = Mid(c, start, i - start)
        Hoja2.Cells(cellcount, 2).Font.Color = c.Characters(start:=start, Length:=1).Font.Color
        'Debug.Print start, i, cellcount, Mid(c, i, 1)
        If Len(Hoja2.Cells(cellcount, 2)) = 1 Then cellcount = cellcount - 1
        cellcount = cellcount + 1
        start = i
      End If
  Next i
  Hoja2.Cells(cellcount, 2) = Mid(c, start, i - start)
  Hoja2.Cells(cellcount, 2).Font.Color = c.Characters(start:=start, Length:=1).Font.Color
Next c

End Sub

or see attached file

 

[Visor]

Thanks teacher Hui, the macro is too slow, in a larger text it takes ...
And if we pass text without differentiating the color, and we only use a separator and then already in the sheet2, I myself select the color at my discretion?

I upload the file with quotes separator, but if you think it is more convenient another, you place it
or maby is more convenient that I used numbers as separator ....1......... 2...... 3......

 

[Hui]

It is slow as you wanted to separate by color
This means that the VBA has to read every character from Excel and compare it to the previous character, which is super slow

If we separate by a character like ";" it will be super fast, because we can read the whole cell into an array
the processing is all in memory

See this code below:

Sub SeparateTextbyColor()

Dim c As Range
Dim tArr As Variant
Dim cellcount As Integer
Dim i As Long, start As Long

cellcount = 4
Application.ScreenUpdating = False
For Each c In Selection

  tArr = Split(c.Text, ";")
  Hoja2.Cells(cellcount, 2).Resize(UBound(tArr, 1) + 1, 1) = Application.WorksheetFunction.Transpose(tArr)
  cellcount = cellcount + UBound(tArr, 1) + 1
Next c
Application.ScreenUpdating = True
End Sub

or file attached:

 

[Visor]

Excellent!! yes there if it works super-fast.
If I format the color in the destination cell, I will always have the color ready
Thank you Master Hui
While I was waiting for a solution, I tried to do something using userform and although I'm doing it step by step, it worked for me but I was left with a frustration of doing the operation between textbox.
Because this topic is different, I'm going to open a new topic for that problem.
I hope you can have a look

While I am very grateful for the solution provided in this topic