Copy Numbers in Visual Basic (with a line break)

[pecsenye]

Hello everyone,
I would like to solve the following in Visual Basic: In column A, there are two numbers one below the other (with a line break). I want to create a copy of them. Only the top number should be in column C, and only the bottom number should be in column D. It's IMPORTANT that the numbers can have different lengths (for example, 2, 31, 153, etc.) and they are not always the same length.
Thanks for helping!

 

[Snakehips]

@pecsenye Are you averse to a formula solution?

 

[pecsenye]

Hello! I think the formula would also be appropriate, but I will write it after I have tried it.

 

[Snakehips]

Then maybe like below?

Book1
	A	B	C	D	E
1	543 2999		543	2999	<<< Suitable for other than Excel 365
2
3	12 2563		12	2563	<<< Suitable for Excel 365
Sheet6

Cell Formulas
Range	Formula
C1	C1	=LEFT(A1,FIND(CHAR(10),A1)-1)
D1	D1	=MID(A1,FIND(CHAR(10),A1)+1,99)
C3	C3	=TEXTBEFORE(A3,CHAR(10))
D3	D3	=TEXTAFTER(A3,CHAR(10))

HTH

 

[jolivanes]

Change references as and where required.

Sub Try()
Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 1).Offset(, 2).Resize(, 2).Value = Split(Cells(i, 1), Chr(10))
    Next i
End Sub

 

[pecsenye]

Then maybe like below?

Book1
	A	B	C	D	E
1	543 2999		543	2999	<<< Suitable for other than Excel 365
2
3	12 2563		12	2563	<<< Suitable for Excel 365
Sheet6

Cell Formulas
Range	Formula
C1	C1	=LEFT(A1,FIND(CHAR(10),A1)-1)
D1	D1	=MID(A1,FIND(CHAR(10),A1)+1,99)
C3	C3	=TEXTBEFORE(A3,CHAR(10))
D3	D3	=TEXTAFTER(A3,CHAR(10))

HTH

Due to the versions and language differences, Visual Basic would be better. These don't work for me. Although the Excel is old.

 

[Burrgogi]

Due to the versions and language differences, Visual Basic would be better. These don't work for me. Although the Excel is old.

There's no reason why that formula shouldn't work. I have a very old version of Excel and it works perfectly. Which version of Excel are you using? Did you check to make sure that you copied & pasted the formula correctly? You should update your forum profile to display the version of Excel you're using.

 

[jolivanes]

If you want the best of both worlds, half Post#4 (using formulae) and half Post#5 (using macro) you can try this.

Sub With_Formula()
Application.ScreenUpdating = False
With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Offset(, 2).Formula = "=Left(RC[-2], Find(CHAR(10), RC[-2])-1)"
    .Offset(, 3).Formula = "=Mid(RC[-3], Find(CHAR(10), RC[-3])+1, 99)"
    .Offset(, 2).Resize(, 2).Value = .Offset(, 2).Resize(, 2).Value
End With
Application.ScreenUpdating = True
End Sub

 

[pecsenye]

Change references as and where required.

Sub Try()
Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 1).Offset(, 2).Resize(, 2).Value = Split(Cells(i, 1), Chr(10))
    Next i
End Sub

This solution is good, thank you very much!

 

[pecsenye]

If you want the best of both worlds, half Post#4 (using formulae) and half Post#5 (using macro) you can try this.

Sub With_Formula()
Application.ScreenUpdating = False
With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Offset(, 2).Formula = "=Left(RC[-2], Find(CHAR(10), RC[-2])-1)"
    .Offset(, 3).Formula = "=Mid(RC[-3], Find(CHAR(10), RC[-3])+1, 99)"
    .Offset(, 2).Resize(, 2).Value = .Offset(, 2).Resize(, 2).Value
End With
Application.ScreenUpdating = True
End Sub

Thank you very much, I can use this better for the task! Everything works fine! Thank you so much to you and everyone!