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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
louisjunker,

It would help if we can see your actual workbook/worksheets.

And, if you would manually create a new worksheet D1V2, with the results from worksheet D1 that you are looking for?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

Dropbox
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
louisjunker,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

In a copy of your workbook, please remove all macros, and, then rename the copied workbook using the xlsx file extension, and, then repost on dropbox.


Then, in your next reply, also, display all of your macro code:

When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]
 
Last edited:

louisjunker

New Member
Joined
Aug 7, 2017
Messages
12
hiker95,

Ok, I have uploaded the file without macros here:
Dropbox - Book1v2.xlsx

The macros I have is at the moment these two:
Copying Macro:
Code:
 Sub sbCopyRangeToAnotherSheet()
    'Copying Raw Data from Datasheet to D1 sheet.
        Sheets("Data").Range("F2:G100").Copy Destination:=Sheets("D1").Range("A2")
    'Copying Raw Data from Datasheet to D2 sheet.
        Sheets("Data").Range("H2:I100").Copy Destination:=Sheets("D2").Range("A2")
    'Copying Raw Data from Datasheet to D3 sheet.
        Sheets("Data").Range("J2:K100").Copy Destination:=Sheets("D3").Range("A2")
    'Copying Raw Data from Datasheet to D4 sheet.
        Sheets("Data").Range("L2:M100").Copy Destination:=Sheets("D4").Range("A2")
    'Copying Raw Data from Datasheet to D5 sheet.
        Sheets("Data").Range("N2:O100").Copy Destination:=Sheets("D5").Range("A2")
    'Copying Raw Data from Datasheet to D6 sheet.
        Sheets("Data").Range("P2:Q100").Copy Destination:=Sheets("D6").Range("A2")
    'Copying Raw Data from Datasheet to D7 sheet.
        Sheets("Data").Range("R2:S100").Copy Destination:=Sheets("D7").Range("A2")
    'Copying Raw Data from Datasheet to D8 sheet.
        Sheets("Data").Range("T2:U100").Copy Destination:=Sheets("D8").Range("A2")
End Sub
And a Find and replace
Code:
Sub FindAndReplace()
Dim i As Long
For i = 1 To 8
 
    With Sheets("D" & i)
        .Range("A2:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
Next
End Sub
I now need a macro to make the data in the B columns go from text to seperet columns.
So if B2 has the text: "16.09.2017,18.06.2018"
It should make B2: "16.09.2017" and C2: "18.06.2018".

Does it make sense? :biggrin:
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
louisjunker,

Thanks for the workbook/worksheets.


In some of the worksheets there are columns B with text, and, or strikethrus (I can not duplicate these in MrExcel):
16.09.2016,Closed

Closed,12.12.2016

,27.12.2017


strikethrus (I can not duplicate these)
05.09.2016,19.09.2016
24.10.2017,30.11.2016,21.12.2016
,27.12.2017


So that I can get it right on the first try, can I have another workbook, with all of the current worksheets, and, what the following new worksheets results should look like?

Sheet D6 --> Sheet D6V2

Sheet D7 --> Sheet D7V2

Sheet D8 --> Sheet D8V2
 
Last edited:

louisjunker

New Member
Joined
Aug 7, 2017
Messages
12
Hello,


Dropbox - Book1v3.xlsx
I have updated the sheets so D5 (V2) is how D5 should look like after the macro.
I have only done this with D5, D6, D7 and D8 - because with the current data D1 to D4 will not change.

The text "Closed" is because the date is unknown. I don't know how else to show this.
The strikethrus is only used in another excel workbook for my colleague, so she can keep track of the dates and add some comments.
It isn't relevant for my data.

Did it make sense? :)
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
louisjunker,

Here is a macro solution for you to consider that is based on your last reply.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ConvertTextToColumns()
' hiker95, 08/09/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).Value = Application.Transpose(s)
          End If
        End If
      Next r
    End If
    .Columns.AutoFit
  End With
Next a
End Sub
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ConvertTextToColumns macro.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
hiker95,

It almost worked. It made the text to culomn, but it made the wrong text :)

Instead of the correct dates, it just copied the first date and inserted multiple times.

Look at this picture: Dropbox - 2017-08-11 13_02_30-Microsoft Excel - Book1v2.xlsm.png

Do you know how to fix it? ;)
louisjunker,

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

2. Are you using a PC or a Mac?


I can not work with the link in your reply #9.

The macro in my reply #8, did work correctly in your workbook/worksheets in your reply #7.


In order to continue I would have to see your actual raw data workbook, with worksheets, D1, D2, D3, D4, D5, D6, D7, D8.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,006
Messages
5,465,965
Members
406,457
Latest member
Pinky Rose Jordan

This Week's Hot Topics

Top