Copy from notepad and paste in Excel cells

[Ray.Mason]

Hi Guys,

I know how open a notepad file but I'm stuck as to how I copy all text in notepad that starts with # and / and > and ? Characters, copying to A1,B1,C1 etc
then next # and / and > and ? characters to A2,B2,C2 etc until EOF.
I have tried the eternal data import but there is a lot of junk I would need to clean out hence thought a macro that only copies stuff that I need would save me time.

Example of text in notepad;

#Rules
/control
/independence
/Freedom
>1004
?control 1-5
?independence 5-7
?Freedom 7-10
<O> some other lines of text here..........
\\pr\\pr @
..
#Leadership
/Autocratic
/Democratic
/Laissez-faire
>1005
?Leadership
?Autocratic
?Democratic
<O> some other lines of text here..........
\\pr\\pr @


and so on...


Any ideas?

Thanks!

[SamT]

The following is pseudocode. Pseudocode is not code, it is thoughts about code.

Files are read by Lines. Each Line is a single String
For each Line in File
X = Left(line,1)
If X= "#" Or X = "/" Or X = etc Then _
MyRange = Right(Line, 2)
Next

[GarysStudent]

So lines beginning with # always go in column A??

[Ray.Mason]

Thanks Sam,

@GarysStudent that's correct # in column A then / in B,C etc

[GarysStudent]

Then here is our first attempt:

Sub ParseData()
Dim TextLine As String, CH As String
Close #1
Open "C:\TestFolder\RayMason.txt" For Input As #1
Dim s As String
Dim i As Long, j As Long
j = 1
i = 0
Do While Not EOF(1)
Line Input #1, TextLine
CH = Left(TextLine, 1)
If CH = "#" Then
i = i + 1
j = 1
Cells(i, j) = TextLine
End If
If CH = "/" Or CH = ">" Or CH = "?" Then
j = j + 1
Cells(i, j) = TextLine
End If
Loop
Close #1
End Sub


Modify the path and filename to meet you needs.
Post back for the next attempt!

[snb]

Sub M_snb()
sn=split(join(filter(filter(split(createobject("scripting.filesystemobject").opentextfile("G:\OF\sample.txt").readall,vbcrlf),"<",false),"@",false),vblf),"#")

for j=0 to ubound(sn)
sp=split(sn(j),vblf)
cells(j+1,1).resize(,ubound(sp)+1)=sp
next
End sub

[SamT]

I missed the column Assignments.

Select Case Left(line,1)
Case "#"
ColNum = 1
Case etc
ColuNum = etc
Case Else
ColNum = 99
End Select
If ColNum <5 Then Cells(Rownum, ColNum) = Right(Line, 2)
Increment Rownum

[Ray.Mason]

Thanks for all the help guys, Sorry I was away yesterday. GarysStudent, I've tried your code and it sure works like a dream, Many thanks!

I have one issue though, I want to strip out the characters when pasting in excel and attempted changing this but having issues with that line when running macro;

CH = Left(TextLine, 1)
to
CH = Left(TextLine, Len(TextLine) - 1)

Any ideas?

Thanks again!

[Ray.Mason]

Just to add, I had recorded a find and replace macro to run after, but replacing "?" with blank deletes everything in worksheet, Not sure if this is some excel shortcut for erasing everything

[GarysStudent]

Hi Ray:

My code captures the left-most character of the CH string.
You line strips off the right-most character from the CH string.

[SamT]

Oops! My code should readRight(line, 2, Len(Line)) NOT (line, 2).

[Ray.Mason]

Thanks Guys! This works fine now, all thanks to you. I greatly appreciate all your time and effort. I have managed to do some things which I would have never dreamt possible through this forum.

Have a good weekend!

Ray

[GarysStudent]

Thanks for the feedback!