Splitting Cell information and removing every other row

Butch-196 · Aug 16, 2022

I have downloaded some information from an SD card and transferred it into excel. format is below in column A, however I would like the data to be split into separate cells as per columns B,C,D,E,F :-

A	B	C	D	E	F
	Day	Time	Pump Status		Lake Level
Time Stamp,2022072818,Pump on %,100,Level %,21,	28/07/2022	18:00	ON		21%

Time Stamp,2022072819,Pump on %,0,Level %,22,	28/07/2022	19:00	ON		21%

Time Stamp,2022072820,Pump on %,100,Level %,22,	28/07/2022	20:00	ON		21%

Time Stamp,2022072821,Pump on %,0,Level %,21,	28/07/2022	21:00	ON		21%

Time Stamp,2022072822,Pump on %,100,Level %,21,	28/07/2022	22:00	ON		21%

Time Stamp,2022072823,Pump on %,0,Level %,21,	28/07/2022	23:00	ON		21%

Time Stamp,2022072900,Pump on %,100,Level %,21,	29/07/2022	00:00	ON		21%

Thanks for your help

Automatrix · Aug 16, 2022

Power Query is suitable for this.

Split Column --> By Delimiter --> choose Comma --> OK.

You can also filter out empty rows while you're in the query editor.

nemmi69 · Aug 16, 2022

try

VBA Code:

Dim WBook As Workbook
Dim Wsheet As Worksheet
Dim Ploop As Long
Dim Sloop As Long
Dim LastRow As Long
Dim ListArr() As String

Sub SplitData()
Set WBook = ThisWorkbook
Set Wsheet = WBook.ActiveSheet

LastRow = Range("A1").End(xlDown).Row

For Ploop = 1 To LastRow
    ListArr = Split(Wsheet.Range("A" & Ploop).Value, ",")
    Wsheet.Range("B" & Ploop).Value = ListArr
        For Sloop = LBound(ListArr) To UBound(ListArr)
            If Sloop > 0 Then
                Wsheet.Cells(Ploop, Sloop + 1).Value = ListArr(Sloop)
            End If
        Next Sloop
Next Ploop
End Sub

mumps · Aug 16, 2022

Try:

VBA Code:

Sub SplitData()
    Application.ScreenUpdating = False
    Dim x As Long, v As Variant, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 2 To lRow Step 2
        v = Split(Range("A" & x), ",")
        Range("B" & x) = Format(DateSerial(Left(v(1), 4), Mid(v(1), 5, 2), Mid(v(1), 7, 2)), "dd/mm/yyyy")
        Range("C" & x) = Right(v(1), 2) & ":00"
        Range("D" & x) = UCase(Mid(v(2), 6, 2))
        Range("F" & x) = Left(v(5), 2) & "%"
    Next x
    Application.ScreenUpdating = True
End Sub

Butch-196 · Aug 25, 2022

nemmi69 said:

try

VBA Code:

Dim WBook As Workbook
Dim Wsheet As Worksheet
Dim Ploop As Long
Dim Sloop As Long
Dim LastRow As Long
Dim ListArr() As String

Sub SplitData()
Set WBook = ThisWorkbook
Set Wsheet = WBook.ActiveSheet

LastRow = Range("A1").End(xlDown).Row

For Ploop = 1 To LastRow
    ListArr = Split(Wsheet.Range("A" & Ploop).Value, ",")
    Wsheet.Range("B" & Ploop).Value = ListArr
        For Sloop = LBound(ListArr) To UBound(ListArr)
            If Sloop > 0 Then
                Wsheet.Cells(Ploop, Sloop + 1).Value = ListArr(Sloop)
            End If
        Next Sloop
Next Ploop
End Sub

Need some help getting this to work. VBA is all still a little new - could you give me a formula?

mumps · Aug 25, 2022

Have you tried the macro I suggested in Post #4?

Peter_SSs · Aug 25, 2022

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Butch-196 said:
could you give me a formula?

Try these. I have assumed that pump status is either "on" or "off"

22 08 25.xlsm

A

B

C

D

E

F

1

Day

Time

Pump Status

Lake Level

2

Time Stamp,2022072818,Pump on %,100,Level %,21,

28/07/2022