Text to columns while preserving text formatting.

[Sahaj Panchal]

Hi,
This is Sahaj.
A colleague of mine sent me an excel with lots of data. Please refer to the sample I have attached. As you can see some of the text is in green. I want to split the text in different columns while preserving the colours. Possible?
Thanks,
Sahaj.

 

[NARAYANK991]

Hi Sahaj ,

After the text has been separated into several columns , you can use Conditional Formatting to colour the wanted text in green.

See the attached file. I have done it for the text in column B.

Narayan

 

[Sahaj Panchal]

Hi,
But you need to separate those green coloured numbers elsewhere, right?
For 10 rows, it's easy. I have 1239, and counting! Same is true for the column 'Vendor id.'
If you've observed, 3 ID's --> 3 Vendor Ids.
AC10001 --> ZSP-254.36.10-SA#PLK
IC90015 --> VM 12A-94.124.0010#IS
for first row. And likewise.

What my colleague intended to do is to mark those ids which are completed as green. But as it's in a single cell, you'll have to select those ids as a text, and then apply formatting. I want to separate them in single cell as it's easy. Then-after, I can select multiple ids(i.e., cells) and apply formatting at once.
Thanks,
Sahaj.

 

[NARAYANK991]

Hi ,

I have clearly mentioned that I am not talking of the Vendor ID column.

Contrary to what you think , colouring the text in column B after separating it into several columns took less than 5 minutes ; the only actions required were :

1. Insert as many columns between column B and column C , as there are items in any one cell in column B ; thus , if the maximum number of items in any one cell in column B is 6 , then insert 6 additional columns between column B and column C.

2. Do a Text to Columns

3. Put the list of IDs which need to be coloured in a separate part of the worksheet.

4. Introduce a Conditional Formatting rule which will make use of this list of IDs.

The above steps will be the same , and will take the same effort and time , whether you have 5 rows of data or 50,000.

Instead , if you try to colour these IDs if there are multiple entries in one cell , I think it will be more difficult.

Narayan

 

[jindon]

See if this is how you wanted.

Sub test()
    Dim r As Range, m As Object, i As Long
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\S+(\s{1,2})?)+(?=(\s{3,}|$))"
        For Each r In Range("c2", Range("c" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                For i = 0 To .Execute(r.Value).Count - 1
                    Set m = .Execute(r.Value)(i)
                    r(, i + 2).Value = m.Value
                    r(, i + 2).Font.Color = r.Characters(m.firstindex + 1, 1).Font.Color
                Next
            End If
        Next
    End With
End Sub

 

[Sahaj Panchal]

To Jindon,
Thanks.
What you did worked, and it is precisely what I wanted, but I have never used macro nor know anything about it. So can you please brief me on what is happening in the sheet you provided? No need to explain VB, but just what's the code doing?
Thanks,
Sahaj.

To Narayan,
In step #3 you've mentioned to put ids which I want to colour in a separate column, but I have more that 1300 rows by now and those ids are scattered across the sheet column. So it will be tedious to do so. For 5 cells, it's fine.
Thanks,
Sahaj.

 

[jindon]

1) Your data is separated by 3 spaces.
2) Separate each block of data to different column(s) and apply font color from the original data.

Enough?

 

[Sahaj Panchal]

Hi,
For now, yes. I got the concept. Now I am trying to understand what you did, line by line so that I can apply it else where. However, I applied it to other sheets having the same data by changing the range "c2", but it's not working as it should. Especially if my data is separated by, say coma (,). So I guess some high-level coding is there in the macro. That's why I need to understand it.
Anyway, I guess first I need to get familiar with VB for Excel then I can expect to learn something from here. Any tips in doing that?
Thanks a lot,
Sahaj.

 

[jindon]

If separated by a comma

        .Pattern = "[^, ][^,]+(?= *(,|$))"