Convert text to columns multiple sheets

[louisjunker]

Hello,

I'm trying to create a macro in VBA which should convert all cells from B2 and downwards into mutiple columns.
I got 8 sheets called D1, D2, ..., D8 - which all need to be affected by this macro.
The character to seperate will be a comma.
The macro will seperate dates from each other.

I have searched around the internet - and couldn't find anything there was working.

Can anybody help

Best regards
LJ

 

[hiker95]

louisjunker,

I did some checking on your latest supplied workbook.

The information that was displayed in the columns B was NOT text.

I am not able to provide screenshots using the old version of the MrExcel HTML Maker, as I have done in the past, because I am waiting for the new version with installation instructions.

But, the macro I provided, when the information in the columns B is text, worked correctly.

 

[louisjunker]

louisjunker,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

I'm usind a Windows 7 on a PC. The version of Excel is 2010.

To your other reply - is it because the cell is formated as something else than text?

 

[hiker95]

To your other reply - is it because the cell is formated as something else than text?

louisjunker,

When I opened your last workbook from DropBox, all the cells were formatted as General????

And, I had to manually format them as Text.

If I was able to use the MrExcel HTML (worksheet display) product to display screenshots, you would have been able to see my results.

I am now waiting for the next version of MrExcel HTML (worksheet display) product.


Maybe someone else at MrExcel will be able to help you.

 

[louisjunker]

louisjunker,

When I opened your last workbook from DropBox, all the cells were formatted as General????

And, I had to manually format them as Text.

If I was able to use the MrExcel HTML (worksheet display) product to display screenshots, you would have been able to see my results.

I am now waiting for the next version of MrExcel HTML (worksheet display) product.


Maybe someone else at MrExcel will be able to help you.

hiker95,

I tried to format all the cells to text, but it still wont execute the text to columns correctly. It just take the first date and copy it into the columns next to.
So if the first date is 24.06.17, and there is a total of 4 dates in the cell, the 3 cells next to the original will contain "24.06.17".
The macro didn't do that when you tried?

 

[hiker95]

louisjunker,

The following flat text displays may not show correct column alignment.

Sample raw data in worksheet D5:

D5 PostponementD5
Closed
30.06.2016
10.06.2016
11.09.2015
31.12.2015
17.06.2016
24.09.2016
08.07.2016
31.05.2016
10.06.2016
01.12.2016
15.06.2016 14.04.2017,09.06.2017,23.06.2017,18.08.2017
31.01.2017 31.03.2017,25.05.2017,30.07.2017
02.11.2016
27.01.2017 07.02.2017
31.10.2016
20.12.2017 18.04.2017
30.03.2017 29.04.17,30.05.17,30.06.2017
31.03.2017 30.04.2017,12.05.2017,31.05.2017,23.06.2017
15.05.2017



And, after my macro, going out to column E:

D5 PostponementD5
Closed
30.06.2016
10.06.2016
11.09.2015
31.12.2015
17.06.2016
24.09.2016
08.07.2016
31.05.2016
10.06.2016
01.12.2016
15.06.2016 14.04.2017 14.04.2017 14.04.2017 14.04.2017
31.01.2017 31.03.2017 31.03.2017 31.03.2017
02.11.2016
27.01.2017 07.02.2017
31.10.2016
20.12.2017 18.04.2017
30.03.2017 29.04.17 29.04.17 29.04.17
31.03.2017 30.04.2017 30.04.2017 30.04.2017 30.04.2017
15.05.2017

 

[louisjunker]

louisjunker,

The following flat text displays may not show correct column alignment.

Sample raw data in worksheet D5:

D5 PostponementD5
Closed
30.06.2016
10.06.2016
11.09.2015
31.12.2015
17.06.2016
24.09.2016
08.07.2016
31.05.2016
10.06.2016
01.12.2016
15.06.2016 14.04.2017,09.06.2017,23.06.2017,18.08.2017
31.01.2017 31.03.2017,25.05.2017,30.07.2017
02.11.2016
27.01.2017 07.02.2017
31.10.2016
20.12.2017 18.04.2017
30.03.2017 29.04.17,30.05.17,30.06.2017
31.03.2017 30.04.2017,12.05.2017,31.05.2017,23.06.2017
15.05.2017



And, after my macro, going out to column E:

D5 PostponementD5
Closed
30.06.2016
10.06.2016
11.09.2015
31.12.2015
17.06.2016
24.09.2016
08.07.2016
31.05.2016
10.06.2016
01.12.2016
15.06.2016 14.04.2017 14.04.2017 14.04.2017 14.04.2017
31.01.2017 31.03.2017 31.03.2017 31.03.2017
02.11.2016
27.01.2017 07.02.2017
31.10.2016
20.12.2017 18.04.2017
30.03.2017 29.04.17 29.04.17 29.04.17
31.03.2017 30.04.2017 30.04.2017 30.04.2017 30.04.2017
15.05.2017

hiker95,

The same is happening me.
If looking at the second last row it shows this after the macro:
31.03.2017 30.04.2017 30.04.2017 30.04.2017 30.04.2017

But should show this:
31.03.2017 30.04.2017 12.05.2017 31.05.2017 23.06.2017

Where the first date is en column B, second in C ans so on.

 

[hiker95]

louisjunker,

Maybe someone else on MrExcel will be able to help you.

 

[hiker95]

louisjunker,

I found the problem.

Please try the following macro

With the same instructions as my reply #8.

Sub ConvertTextToColumns_V2()
' hiker95, 08/15/2017, ME1017853
Dim wary, a As Long
Dim r As Range, lr As Long, s
wary = Array("D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8")
For a = LBound(wary) To UBound(wary)
  With Sheets(wary(a))
    lr = .Cells(Rows.Count, 2).End(xlUp).Row
    If lr > 1 Then
      For Each r In .Range("B2:B" & lr)
        If Not r = vbEmpty Then
          If InStr(r, ",") Then
            s = Split(r, ",")
            r.Resize(, UBound(s) + 1) = s
          End If
        End If
      Next r
    End If
    .Columns.AutoFit
  End With
Next a
End Sub

With the same instructions as my reply #8.

Then run the ConvertTextToColumns_V2 macro.

 

[louisjunker]

hiker95,

Amazing - it worked :D
Thank you so much!

 

[hiker95]

louisjunker,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.