How do i automaticcally Relocate recognized data to a new worksheet?

[Rbeusink]

Hi Guys,

Im stuck for a while now, I was wondering if you guys could help me out.
Ive spent hours on the internet looking up this issue but still im stuck clueless. ( And I am aware that this problem may seem weird to you guys)

So here's whats up.

I have a document containing text. And I want excel to scan this text and filter out several word sequences.
Now filtering these different sequences is not the issue. What I want to do now is put a code in that recognizes parts of the text.
And I want the code to write down all the filtered stuff into a second or alternative worksheet.

Short example: Let's say I've copied a story about marbles.
Ive written a code that recognizes all the different types of marbles.

But now I want excel to automaticcaly poop out all these different marble types that where recognized into a new sheet.

Is anbybody out there who could help me get started a little?

Best regards,Robin

[Richard Buttrey]

Hi Robin and welcome to the forum,

Will you share your workbook and code with us?
We generally like to see these sorts of requests in context. On the results sheet add some manually determined results so that we can ensure any code we give you will do the job.

[Rbeusink]

Hey Richard Thanks this is the code I have so far. The problem is, in de text there could be several of this sequences. And we would like to put all of these results in another excel sheet, in 1 row.

We have tried to make a loop over the second part of the code to remember the last position Mid(Text, x+Len(Sequence), Len(characters-(x+Len(Sequence))), but we couldn't get this to work.

The excel sheet is just one column full of numbers/letters and characters.

We hope we have informed you enough.

Public Function Sequence(Text As String) As String

Dim characters As String

Dim i As Integer
Dim x As Integer

'First we make the text so the sequence can be recognized

For i = 1 To Len(Text)

If IsNumeric(Mid(Text, i, 1)) Then
characters = characters + "N"

ElseIf Mid(Tekst, i, 1) Like "[A-Z]" Then
characters = characters + "A"

Else

characters = characters + Mid(Text, i, 1)

End If

Next

'Then we try to find the first matching sequence and if its found return it

If characters Like "*NAAAN*" Then
x = InStr(characters, "NAAAN")
Sequence = Mid(Text, x, Len("NAAAN"))

ElseIf characters Like "*ANANA*" Then
x = InStr(characters, "ANANA")
Sequence = Mid(Text, x, Len("ANANA"))


Else

Sequence = "No Sequence"

End If

End Function

[Rbeusink]

Sorry there was one typo.

Public Function Sequence(Text As String) As String

Dim characters As String

Dim i As Integer
Dim x As Integer

'First we make the text so the sequence can be recognized

For i = 1 To Len(Text)

If IsNumeric(Mid(Text, i, 1)) Then
characters = characters + "N"

ElseIf Mid(Text, i, 1) Like "[A-Z]" Then
characters = characters + "A"

Else

characters = characters + Mid(Text, i, 1)

End If

Next

'Then we try to find the first matching sequence and if its found return it

If characters Like "*NAAAN*" Then
x = InStr(characters, "NAAAN")
Sequence = Mid(Text, x, Len("NAAAN"))

ElseIf characters Like "*ANANA*" Then
x = InStr(characters, "ANANA")
Sequence = Mid(Text, x, Len("ANANA"))


Else

Sequence = "No Sequence"

End If

End Function

[Rbeusink]

Could anybody please have a look at this?
Unfortunately I cannot upload any workbook or printscreen. But I assume the stated is kind of clear.

Thanks

[Richard Buttrey]

Hi,

Any particular reason why you can't upload the workbook? If it contains sensitive data just anonymise it.

[Rbeusink]

maybe this works

[Richard Buttrey]

Hi,

Can you explain in a narrative way, referencing your worksheet cells, exactly what you are trying to do. i.e. what are the rules that you're applying to column A to get column B.

Presumably you are looking for results in column B but I'm not clear exactly what.

[Rbeusink]

Thanks for having a look at my issue,

The whole idea of my question is that the code wil recognize specific sequences/ ''codes'' in a text. So lets say I got a pdf I copy the stuff to excel. Then l want my code to say if the given text contains a sequence which is interesting for me.
Saying if the cell before contains a sequence or not and if so what it exactly is. I've succeeded now that my code can automaticcaly recognize all the sequences(which was the first part of my question see code below), The next thing is that the code needs to generate the outcome on another worksheet. But I cant figure out how that works.


x = 0
Sequence = ""
z = 1
v = 0

Do Until Sequence = "No Sequence"

If Mid(characters, x + z, (Len(characters) - (x - 1))) Like "*NAAAN*" Then
y = InStr(Mid(characters, x + z, (Len(characters) - (x - 1))), "NAAAN")
x = x + y - v
Sequence = Mid(Text, x, Len("NAAAN"))
x = x + (Len(Sequence))
v = 1
Else

Sequence = "No Sequence"

End If

z = 0
Sequence2 = Sequence2 & " " & Sequence

Loop

[Richard Buttrey]

Hi,

I'm still not understanding which is why I wanted to understand your data and results with reference to specific cells, and the thought process you go through to get the result.

For instance what are the codes you're looking for? I was expecting to see a cell in the workbook that would be used to compare with your data.
Then I expected a rule to be something like 'find this code string somewhere in the column A cell and then report it in some defined way.

Or a definition of what constitutes a 'sequence' and then analyse column A cells and decide accordingly

I'm less interested in the current code you have since I'd like to consider the aim and not be curtailed by what you currently do. Which may indeed be the only/best way of doing it, but one of the reasons for posting here is to surely establish the most efficient way.

[Rbeusink]

Sorry if I am being to vague.

The thing is. In the text-document are hidden codes. Codes that I need to ''filter'' from the text document.
Since these codes differ from being numbers and letters there are a lot of different combinations.
The script needs to recognize if a word is one of my code- sequences that I need as an output.

What I did now is making a script which is capable of recognizing all the different codes in the text file.
If it is a code it will put out the code as it was written. If it is not a sequence code it will say No sequence.

By doing this I was capable of filtering all the right codes in the document for my output.
But What would be the nicest is. If my script could recognize all the correct codes and process them to a new worksheet.
And also each code in its own cell. So maybe you could help me with a different approach or an additional script that helps me with the worksheet issue.

Great thanks for trying to help me out.