Convert text to columns multiple sheets

louisjunker

New Member
Joined
Aug 7, 2017
Messages
12
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.:confused:

Can anybody help :)

Best regards
LJ
 
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.
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
louisjunker,

I found the problem.

Please try the following macro

With the same instructions as my reply #8.

Code:
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.
 
Last edited:
Upvote 0
louisjunker,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top