Return in cell

[DEllis]

Hello everyone. so I have a spreadsheet that looks like this

Number	date/time
UPH0015429 UPH0015428	12/15/2024 20:39:16
UPH0015426 UPH0015425	01/05/2025 09:36:12

What I need is to have the number column take the bottom number out put it in a new line beneath the number and remove the return... so like this (note date and time for second number in cell is same as first)

Number	Date/time
UPH0015429	12/15/2024 20:39:16
UPH0015428	12/15/2024 20:39:16
UPH0015426	01/05/2025 09:36:12
UPH001542	01/05/2025 09:36:12

I am okay if the data cannot align with the second number, but I do need the numbers separated right beneath each other. The issue I have is some lines there is no second number just one, so that line is okay.

 

[MikeVol]

Hello @ DEllis.
Maybe I didn't understand you, but here is a possible solution to your question using vba code.

Option Explicit

Sub SplitCells()
    Dim j           As Long
    Dim valuesArr   As Variant
    Dim insertRow   As Long

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")    ' Specify name of your worksheet

    Dim lastRow     As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i           As Long
    i = 2    ' Let's start with the second row, if you have a different row, then change it
    Application.ScreenUpdating = False

    Do While i <= lastRow

        If ws.Cells(i, 1).Value <> "" Then
            valuesArr = Split(ws.Cells(i, 1).Value, vbLf)

            If UBound(valuesArr) > 0 Then
                ws.Cells(i, 1).Value = Trim(valuesArr(0))
                insertRow = i + 1

                For j = 1 To UBound(valuesArr)
                    ws.Rows(insertRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    ws.Cells(insertRow, 1).Value = Trim(valuesArr(j))
                    ws.Cells(insertRow, 2).Value = ws.Cells(i, 2).Value

                    insertRow = insertRow + 1
                    lastRow = lastRow + 1
                Next j

            End If

        End If

        i = i + 1
    Loop

    Set ws = Nothing
    Application.ScreenUpdating = True
    MsgBox "Cell splitting complete!", vbInformation
End Sub

I hope I understood you correctly and was able to help you. Good luck.

 

[Peter_SSs]

Is this what you are after?

25 02 21.xlsm
	A	B	C	D	E
1	Number	date/time		Number	date/time
2	UPH0015429 UPH0015428	15/12/2024 20:39:16		UPH0015429	15/12/2024 20:39:16
3	UPH0015427	12/12/2024 20:02:25		UPH0015428	15/12/2024 20:39:16
4	UPH0015426 UPH0015425	05/01/2025 09:36:12		UPH0015427	12/12/2024 20:02:25
5				UPH0015426	05/01/2025 09:36:12
6				UPH0015425	05/01/2025 09:36:12
new lines

Cell Formulas
Range	Formula
D2:E6	D2	=LET(n,TEXTSPLIT(TEXTJOIN(CHAR(10),,A2:A4),,CHAR(10)),HSTACK(n,VLOOKUP("*"&n&"*",A2:B4,2,0)))
Dynamic array formulas.

 

[DEllis]

Is this what you are after?

25 02 21.xlsm
	A	B	C	D	E
1	Number	date/time		Number	date/time
2	UPH0015429 UPH0015428	15/12/2024 20:39:16		UPH0015429	15/12/2024 20:39:16
3	UPH0015427	12/12/2024 20:02:25		UPH0015428	15/12/2024 20:39:16
4	UPH0015426 UPH0015425	05/01/2025 09:36:12		UPH0015427	12/12/2024 20:02:25
5				UPH0015426	05/01/2025 09:36:12
6				UPH0015425	05/01/2025 09:36:12
new lines

Cell Formulas
Range	Formula
D2:E6	D2	=LET(n,TEXTSPLIT(TEXTJOIN(CHAR(10),,A2:A4),,CHAR(10)),HSTACK(n,VLOOKUP("*"&n&"*",A2:B4,2,0)))
Dynamic array formulas.

Hi there, I put this formula into D2 and in E2 I get Invalid cell reference error. What you showed is exactly what I need, but I can't seem to make it work in my spreadsheet.

 

[DEllis]

Hi there, I put this formula into D2 and in E2 I get Invalid cell reference error. What you showed is exactly what I need, but I can't seem to make it work in my spreadsheet.

Thank you

Hello @ DEllis.
Maybe I didn't understand you, but here is a possible solution to your question using vba code.

Option Explicit

Sub SplitCells()
    Dim j           As Long
    Dim valuesArr   As Variant
    Dim insertRow   As Long

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")    ' Specify name of your worksheet

    Dim lastRow     As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i           As Long
    i = 2    ' Let's start with the second row, if you have a different row, then change it
    Application.ScreenUpdating = False

    Do While i <= lastRow

        If ws.Cells(i, 1).Value <> "" Then
            valuesArr = Split(ws.Cells(i, 1).Value, vbLf)

            If UBound(valuesArr) > 0 Then
                ws.Cells(i, 1).Value = Trim(valuesArr(0))
                insertRow = i + 1

                For j = 1 To UBound(valuesArr)
                    ws.Rows(insertRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    ws.Cells(insertRow, 1).Value = Trim(valuesArr(j))
                    ws.Cells(insertRow, 2).Value = ws.Cells(i, 2).Value

                    insertRow = insertRow + 1
                    lastRow = lastRow + 1
                Next j

            End If

        End If

        i = i + 1
    Loop

    Set ws = Nothing
    Application.ScreenUpdating = True
    MsgBox "Cell splitting complete!", vbInformation
End Sub

I hope I understood you correctly and was able to help you. Good luck.

Thank you

 

[Cubist]

Note that Peter's suggestion won't work if you have repeated values in Number column. An alternative in G and H.

Book2
	A	B	C	D	E	F	G	H
1	Number	date/time		Number	date/time		Number	date/time
2	UPH0015429 UPH0015428	12/15/2024 20:39:16		UPH0015429	12/15/2024 20:39:16		UPH0015429	12/15/2024 20:39:16
3	UPH0015427	12/12/2024 20:02:25		UPH0015428	12/15/2024 20:39:16		UPH0015428	12/15/2024 20:39:16
4	UPH0015429 UPH0015425	1/5/2025 9:36:12		UPH0015427	12/12/2024 20:02:25		UPH0015427	12/12/2024 20:02:25
5				UPH0015429	12/15/2024 20:39:16		UPH0015429	1/5/2025 9:36:12
6				UPH0015425	1/5/2025 9:36:12		UPH0015425	1/5/2025 9:36:12
Sheet3

Cell Formulas
Range	Formula
D2:E6	D2	=LET(n,TEXTSPLIT(TEXTJOIN(CHAR(10),,A2:A4),,CHAR(10)),HSTACK(n,VLOOKUP("*"&n&"*",A2:B4,2,0)))
G2:G6	G2	=TEXTSPLIT(TEXTJOIN(CHAR(10),,A2:A4),,CHAR(10))
H2:H6	H2	=LET(c,LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+1,TOCOL(IFS(SEQUENCE(,MAX(c))<=c,B2:B4),2))
Dynamic array formulas.

 

[Peter_SSs]

I put this formula into D2 and in E2 I get Invalid cell reference error.

It would be good to see a small set of sample data with XL2BB where you are getting that error.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)