According to your attachment a VBA demonstration for starters :
PHP Code:
Sub Demo1()
W = Application.GetOpenFilename("Text Files,*.txt"): If W = False Then Exit Sub
[A1].CurrentRegion.Offset(1).Clear
L& = FreeFile
Open W For Input As #L
W = Split(Input(LOF(L), #L), vbCrLf)
Close #L
ReDim V(1 To UBound(W), 1)
For Each X In W
L = Val(X)
If X Like L & ". *" Then R& = R& + 1: V(R, 0) = L: V(R, 1) = Split(X, , 2)(1) Else V(R, 1) = V(R, 1) & vbLf & X
Next
With [A2:B2].Resize(R)
.HorizontalAlignment = xlLeft
.Value = V
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon ? ★ Add Reputation ? ! ◄ ◄
Thank you very much.
But if the .txt file is big (10 or 20 MB) I get an issue here: L = Val(X)
Ps. Error showing L = Val(X) and run time error type mismatch 13
One more thing is please find the attached txt file and see how is it giving the result.
I am unable to attach a new file. And if I change the number 5 content to the below text I get a different result:
5. How to copy text in rows betwen numbers from a text file
.
How to copy text in rows betwen numbers from a text file
81911. How to copy text in rows betwen numbers from a text file
How to copy text in rows betwen numbers from a text file
1911. How to copy text in rows betwen numbers from a text file
How to copy text in rows betwen numbers from a text file
1911. How to copy text in rows betwen numbers from a text file
1911. How to copy text in rows betwen numbers from a text file
Last edited by jai0000; 09-10-2022 at 04:22 AM.
Reason: updated to clarify and run time error type mismatch 13
According to your attachment a VBA demonstration for starters :
PHP Code:
Sub Demo1()
W = Application.GetOpenFilename("Text Files,*.txt"): If W = False Then Exit Sub
[A1].CurrentRegion.Offset(1).Clear
L& = FreeFile
Open W For Input As #L
W = Split(Input(LOF(L), #L), vbCrLf)
Close #L
ReDim V(1 To UBound(W), 1)
For Each X In W
L = Val(X)
If X Like L & ". *" Then R& = R& + 1: V(R, 0) = L: V(R, 1) = Split(X, , 2)(1) Else V(R, 1) = V(R, 1) & vbLf & X
Next
With [A2:B2].Resize(R)
.HorizontalAlignment = xlLeft
.Value = V
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon ? ★ Add Reputation ? ! ◄ ◄
Help me. I believe a little modification can solve this.
Re: How to copy text in excel cell in rows between numbers from a text file.
If the .txt file is big (10 or 20 MB) I get a problem here: L = Val(X) showing run time error type mismatch 13. I request a solution for this only.
The file attached here is a demo file sir.
Re: How to copy text in excel cell in rows between numbers from a text file.
.Resize(UBound(a, 1), 2) = a gets highlighted in yellow.
Can it be like "Jump to the next number if the current number in the series fails to copy and paste?
Re: How to copy text in excel cell in rows between numbers from a text file.
About question number 1: I think my file may have some formatting issues. I have tried the VBA in different ways. Say there is some issue in the text of number 1111 and then if I delete some text there then the VBA again works up till the next issue say at 2222. If I again delete (randomly a few lines) some text under 2222 then the VBA again works. Really don't know yet what is that. I will post the mock data tomorrow.
LinkBack URL
About LinkBacks
Register To Reply
Re: How to copy text in excel cell in rows between numbers from a text file.
Try this !
Originally Posted by Marc L
Bookmarks