VB to Select & Print Data following Occurrences in a String

[SStrange]

So I have some VB to count the number of occurrences of groupings of characters. What I need to do now is select each of them and have them print the text. To be more specific, I had instances of carriage returns and hard returns in a data string. To be able to select within the string, I removed one character and replaced the other with a separate special character. That allowed me to find the beginning of the individual strings by using the special character (specifically CHR(8)). Basically now there is a string in my data that is (if you use this formula in excel) =CHAR(8)&"A1:". the thing is there could be 5-80 occurrences in the strings. I want it to pull the data for each one selecting as below....

A1: (1) 12345
A1: (2) 13524
A1: (3) 12453
A1: (4) 12534
A1: (5) 31425
etc...

Any help you can offer would be appreciated.

Thanks

 

[ZVI]

Try tweaking this code:

Sub Test()
 
  Const TestData = "A1: (1) 12345" & vbTab & "A1: (2) 13524" & vbTab & "A1: (3) 12453"
 
  Dim a
 
  a = Split(TestData, vbTab)
  Range("D1").Resize(UBound(a) + 1).Value = WorksheetFunction.Transpose(a)
 
End Sub

 

[ZVI]

And in case raw data with vbCR and vbLF symbols is in the cell A1 the full code might be as follows:

Sub Test1()
 
  Dim a
  Dim Txt As String
 
  ' Put raw data from A1 to the Txt variable
  Txt = Range("A1").Value ' It is assumed that raw text data is in the cell A1
 
  ' Delete vbCR char
  Txt = Replace(Txt, vbCr, vbNullString)
 
  ' Split into array by vbLF char
  a = Split(Txt, vbLf)
 
  ' Clear destination column D
  Range("D1", Cells(Rows.Count, "D").End(xlUp)).ClearContents
 
  ' Put the splitted data to the column D
  Range("D1").Resize(UBound(a) + 1).Value = WorksheetFunction.Transpose(a)
 
End Sub

 

[SStrange]

So apparently this is being viewed as an excel question. It's not. This is for Access. For excel I would be able to use a formula to find uniques and have it count on frequency. That I understand. The problem is that the data comes in as follows:

A1*1*100*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426
A1*2*100*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426
A1*3*125*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426
A1*4*125*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426
A1*5*150*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426
A1*6*100*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426
A1*7*100*12345*65482
A2*321654*44545*65421*12345
A3*1000*1001*1002*1003
A4*32151*65481*654214*54127
A5*64821*65412*68251*12426

You can see a recursive pattern of A1, A2, A3, A4, A5 but this is not one line per line of the text. This is all one line of text with the Carriage returns still in the export. What I have done is prior is take the character 10 (line break) out and replace it with another character allowing me to search for an identifier. The data will always be preceded by the special character I used to replace the carriage return and A1*, so I am using that as an identifier. Now I know that finding the first instance would be simple as I can just look for the A1* and use that as a start point and the special character as an end point but then my start point has to adjust each time to find the next occurrence. I would like it print each line and use code to adjust those start and end points. The other option would be to have it split the text into an array (which your VB tried to do) and use a query on the selected data to filter to the data I was looking for. This may need to be moved as I thought I had put this question in the access forum.

Either way, thanks.

 

[ZVI]

My bad, I filtered Zero Replay Posts and even not recognized it's question in Access forum
Could you please post the expected result for the data of post #4?

 

[Joe4]

My bad, I filtered Zero Replay Posts and even not recognized it's question in Access forum

It wasn't when you replied to it.
I think it was initially posted to the Access forum, but when we saw the Excel formula, it was moved to the Excel forum.
I just moved it back a little while ago.

 

[SStrange]

Preferably what I would like one of the export types below. Keep in mind all of this data is in one record right now. I think the second one would be possibly easier and more usable for more projects but either will do for the time being.

Serial Date A1 A2 A3 A4 A5
12345 1/1/2016 A1*1*100*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426
12345 1/1/2016 A1*2*100*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426
12345 1/1/2016 A1*3*125*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426
12345 1/1/2016 A1*4*125*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426
12345 1/1/2016 A1*5*150*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426
12345 1/1/2016 A1*6*100*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426
12345 1/1/2016 A1*7*100*12345*65482 A2*321654*44545*65421*12345 A3*1000*1001*1002*1003 A4*32151*65481*654214*54127 A5*64821*65412*68251*12426


Serial Date Data
12345 1/1/2016 A1*1*100*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426
12345 1/1/2016 A1*2*100*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426
12345 1/1/2016 A1*3*125*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426
12345 1/1/2016 A1*4*125*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426
12345 1/1/2016 A1*5*150*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426
12345 1/1/2016 A1*6*100*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426
12345 1/1/2016 A1*7*100*12345*65482
12345 1/1/2016 A2*321654*44545*65421*12345
12345 1/1/2016 A3*1000*1001*1002*1003
12345 1/1/2016 A4*32151*65481*654214*54127
12345 1/1/2016 A5*64821*65412*68251*12426

 

[ZVI]

It wasn't when you replied to it.
I think it was initially posted to the Access forum, but when we saw the Excel formula, it was moved to the Excel forum.
I just moved it back a little while ago.

Joe, thank you for the clarification!

 

[ZVI]

Serial Date A1 A2 A3 A4 A5
12345 1/1/2016 A1*1*100*12345*65482 A2*...

Where DATE is getting from?

 

[SStrange]

So the original record has a serial and a date of when the data was captured. The record also has the data captured associated with it. The data is all on one record. It's not recording each data line separately.