Find specific lines then find data within those lines - that begins after a specific string

[User45]

Hello,
Hoping someone can help me with this complicated task.What I have is a file with hundreds of lines (all in Column A; like A1,A2,A3...):

cell1
cell2
>>>>cell3\randomdata\city\London\randomdata\randomdata\country\England*&^\randomdata\
cell4
>>cell5 Language: English
cell6
cell7
>>>>cell8\randomdata\randomdata\randomdata\randomdata\country\S*pai^n\randomdata\city\Madrid\
cell9
cell10
cell11
>>cell12 Language: Spanish
cell13
...

In cell3 and cell8 ,the word "city" and the name of the city are always next to each other(same is true for country), as shown above, and they are always separated with a backslash "\". Cells that contain this info always begin with ">>>>".The name of the country can have special characters, as shown on cell3 and cell8.All characters except for "%" are possible.The data we need to extract(city/country names) are always between "\" characters,which basically indicates the beginning and end of the data we need to extract.
Cell5 and Cell12 contain a language info.These cells will always begin with ">>" and the name of the language is always listed after "Language: ".There is no other info in Cell5 and Cell12.There is only one instance of ":" character.
Cell5's info is associated with Cell3's info(and cell12 to cell8) and it always comes after it, but not necessity on the very next cell.As shown above, there can be multiple cells between cells that begin with ">>>>" and ">>".


The goal is to have:

city\London\country\England*&^\Language\English
city\Madrid\country\S*pai^n\Language\Spanish

I'm using Excel2013. Any help is appreciated !!

 

[User45]

Until now I was using it on a small amount of data and it was working fine.Once I started to process more data this error came up:
Run-time error '1004':
Application-defined or object-defined error

The error happens only if the script has to write more than 176 lines on Column B.It reads hundreds of lines from Column A and every time it finds >>>> and >> it will process them and output one cell on B column.So if Column A has 177 pairs of >>>> and >> then the script will not work.I tested this with random data.It always works when it has to deal with equal or less than 176 lines on Column B.
The total number of cells on Column A does not matter.The only thing that matters is the number of >>>> and >> cells (combined they should not be more than 352 , which outputs 176 lines on Column B)
I'm using the script from Post #9. Any thoughts ?

 

[taurean]

It most probably is throwing error when there's data in the range of ~65K rows. This occurs on Application.Transpose. Is that the case?

If you could try out the code I have posted and see if it works then I will tweak it for your "continent" needs.

 

[User45]

I created a new Excel file and tested the code from Post#9 and it gave an error.There was no data after 65k range.
Then I used your code from Post#5 and it worked fine, as long as every ">>>>" cell contains city/country/region data (which isn't always the case).

 

[Rick Rothstein]

Until now I was using it on a small amount of data and it was working fine.Once I started to process more data this error came up:
Run-time error '1004':
Application-defined or object-defined error

Can you email me a copy of your workbook for my code will not work (I need the raw file... the one with the data before any code has been run against it) so I can see what can be done to make my code work with it? My email address is...

rick DOT net AT verizon DOT net

 

[User45]

Mailer Daemon says email does not exist.Could you please verify it's
rick DOT net AT verizon DOT net

 

[Rick Rothstein]

Mailer Daemon says email does not exist.Could you please verify it's
rick DOT net AT verizon DOT net

Sorry, bad typing on my part, this is the correct email address...

rick DOT news AT verizon DOT net

 

[Rick Rothstein]

Here is my revised code which is still kind of short-ish and should now work correctly with your data... and, as a side benefit, it should be much, much faster than the code I posted originally.

Sub ExtractData()
  Dim X As Long, Z As Long, DataIn As Variant, DataOut As Variant
  DataIn = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  ReDim DataOut(1 To UBound(DataIn) + 1, 1 To 1)
  For X = 1 To UBound(DataIn)
    If InStr(1, DataIn(X, 1), "city", 1) Then
      Z = Z + 1
      DataOut(Z, 1) = "city\" & Split(Split(DataIn(X, 1), "\city\")(1), "\")(0)
      DataOut(Z, 1) = DataOut(Z, 1) & "\country\" & Split(Split(DataIn(X, 1), "\country\", , 1)(1), "\")(0)
      DataOut(Z, 1) = DataOut(Z, 1) & "\Language\" & Chr$(1)
      If InStr(1, DataIn(X, 1), "continent", 1) Then
        DataOut(Z, 1) = DataOut(Z, 1) & "\continent\" & Split(Split(DataIn(X, 1), "\continent\", , 1)(1), "\")(0)
      End If
    ElseIf InStr(1, DataIn(X, 1), "Language:", 1) Then
      DataOut(Z, 1) = Replace(DataOut(Z, 1), Chr$(1), Trim(Mid(DataIn(X, 1), InStr(DataIn(X, 1), ":") + 1)))
    End If
  Next
  Range("B1:B" & Z) = DataOut
End Sub