How to copy text in excel cell in rows between numbers from a text file.

**jai0000** · 09-01-2022, 06:22 AM

How to copy the text in excel cells in rows between numbers from a text file.
The last line does not have a number. There are thousands of numbers.

Text in text.txt file Example

1. How to copy the text in rows
between numbers from a text file.

How to copy the text in rows between numbers from a text file.
How to copy the text in rows between numbers from a text file.

How to copy the text in rows between numbers from a text file.

3. How to copy the text in rows between numbers from a text file.

5. How to copy
the text in rows
between numbers
from a text file
.
How to copy the text in rows between numbers from a text file

Output in the excel sheet will look like:

1	How to copy the text in rows between numbers from a text file. How to copy the text in rows between numbers from a text file. How to copy the text in rows between numbers from a text file. How to copy the text in rows between numbers from a text file.
3	How to copy the text in rows between numbers from a text file.
5	How to copy the text in rows between numbers from a text file . How to copy the text in rows between numbers from a text file

**Marc L** · 09-01-2022, 06:36 AM

Your code just must detect if a text line starts with a number via the VBA Like operator ...

For more help don't forget to attach at least a sample source text file and for convenience the expected result workbook.

**jai0000** · 09-01-2022, 09:33 AM

I have attached a sample source text file and for convenience the expected result workbook.
Help.

**Marc L** · 09-07-2022, 11:35 AM

Thanks for the rep' !

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 ? ! ◄ ◄

**jai0000** · 09-07-2022, 03:24 PM

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

**jai0000** · 09-11-2022, 06:55 AM

Originally Posted by Marc L

Thanks for the rep' !

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.

**Marc L** · 09-11-2022, 10:27 AM

Solve what ? As no issue on my side with your attachments ...

**jai0000** · 09-11-2022, 12:43 PM

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.

**Marc L** · 09-11-2022, 12:53 PM

As guessing can't be coding so you must at least check the variable X content when the error raises ...

**jai0000** · 09-11-2022, 02:02 PM

Originally Posted by Marc L

As guessing can't be coding so you must at least check the variable X content when the error raises ...

OK. Sir, is it possible to find, in which line of the text file has an error when I run the VBA?
I also got Overflow (Error 6).

**Marc L** · 09-11-2022, 05:53 PM

Now that's just confusing ! And you can check in debug mode when the error raises ...

**jai0000** · 10-07-2022, 08:08 PM

If there are hundreds of lines between numbers then .Value = V gets highlighted in yellow.

**jindon** · 10-08-2022, 01:58 AM

See if this works

Please Login or Register  to view this content.

**jai0000** · 10-10-2022, 02:50 AM

.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?

**jindon** · 10-10-2022, 04:16 AM

Are you testing the code with the text file(s) that you uploaded?

It is working, so upload the text file that you are working with.

**jai0000** · 10-10-2022, 06:05 AM

can I inbox you the part of the file? I can not share it here. Hope you understand.

How can I add "56438(*&" before these numbers in my .txt file?

**jindon** · 10-10-2022, 06:16 AM

Can you not create a mockup dummy data following the structure of your original file?

If we are dealing with different data, this will never be solved.

I don't understand the 2nd question.

**jai0000** · 10-10-2022, 06:25 AM

I will make a mock data and will post asp.

About 2nd question

From: Text in text.txt file Example:

1. How to copy the text in rows
between numbers from a text file.

How to copy the text in rows between numbers from a text file.
How to copy the text in rows between numbers from a text file.

How to copy the text in rows between numbers from a text file.

3. How to copy the text in rows between numbers from a text file.

5. How to copy
the text in rows
between numbers
from a text file

To: Text in text.txt file Example:

56438(*& 1. How to copy the text in rows
between numbers from a text file.

How to copy the text in rows between numbers from a text file.
How to copy the text in rows between numbers from a text file.

How to copy the text in rows between numbers from a text file.

56438(*& 3. How to copy the text in rows between numbers from a text file.

56438(*& 5. How to copy
the text in rows
between numbers
from a text file

**jindon** · 10-10-2022, 06:27 AM

OK, I see, but if the code is not working, it is useless anyway.

**jai0000** · 10-10-2022, 06:34 AM

I think, If I can add 56438(*& before these numbers in the text.txt file, using another VBA I can achieve the same result without any issue.

**jindon** · 10-10-2022, 06:36 AM

Then you should ask it to the author of that code.

**jai0000** · 10-10-2022, 06:54 AM

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.

How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Try this !

Re: Try this !

Re: Try this !

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Re: How to copy text in excel cell in rows between numbers from a text file.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] HELP with code for copy of some rows from Text file to Excel cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions