Extracting Data from long string

Dave Punky · Sep 24, 2011

Hi all,

I have an idea how to do this, but I've not been having much luck.

Basically what I have is a long string with each section split by a tab. Here is some sample data to explain what I mean:

Code:

Ref:               2Ref:             Code:             Date:            Address:

1000000,           21111111          0123,456789,     01/09/2011,       MR X., Address Line 1, Address Line 2, Zip Code
1000001,           21116111          0123,785412,     01/09/2011,       MR Y., Address Line 1, Address Line 2, Zip Code
1000002,           21221452          0123,874114,     01/09/2011,       MR Z., Address Line 1, Address Line 2, Zip Code

Basically what I need to do is extract each 3rd section (code section) from each line and take that data, then loop and take the 3rd section from the next line down.

I don't want to put the data into a worksheet (which is why I'm not using Excels import data function) as I'm then going to take that data and complete an action, then loop it and start again.

So using the data above, I basically want to extract the data like so:

Code:

Dim refcode as string
Dim alldata as variant

alldata = split(Table Of Data, vbTab)

Do while X <= Last Array Entry in alldata
     refcode = 0123,456789, (then 0123,785412, and then 0123,874114,)
     call action(refcode)

X = X+1
Loop

I was thinking of using Split to split the whole string (as each section being split by a tab I can set this as the split condition) but the problem I have is trying to work a loop to cycle through the whole split variant, and how to determine when the end of the loop is also.

The end of each line is also separated by an enter character so I'm not sure if it would be better to split each line first and then take the data or just by splitting by tabs?

I'm not sure if I've explained this very well, but hopefully somebody can point me in the right direction!

AlphaFrog · Sep 24, 2011

Example: This splits by Lines and then by entries in each line.

 Dim vLines As Variant, vEntries As Variant 'Split data by lines vLines = Split(Table_Of_Data, vbCrLf) For i = LBound(vLines) To UBound(vLines) 'Split lines by entries vEntries = Split(vLines(i), vbTab) 'Test third entry (base zero so third entry is index #2) If vEntries(2) = "0123,785412," Or vEntries(2) = "0123,874114," Then Call action(vEntries(2)) End If Next i

Rick Rothstein · Sep 24, 2011

I'm not entirely clear on your "long string" of data. Is the below quoted data all in a single non-array String variable?

Dave Punky said:

Basically what I have is a long string with each section split by a tab. Here is some sample data to explain what I mean:

Code:

Ref:               2Ref:             Code:             Date:            Address:
 
1000000,           21111111          0123,456789,     01/09/2011,       MR X., Address Line 1, Address Line 2, Zip Code
1000001,           21116111          0123,785412,     01/09/2011,       MR Y., Address Line 1, Address Line 2, Zip Code
1000002,           21221452          0123,874114,     01/09/2011,       MR Z., Address Line 1, Address Line 2, Zip Code

If so, can we assume the separate lines of text that you show are separated by Line Feed characters or, if you read it in from a text file, Windows NewLine character sequences (Carriage Return followed by a Line Feed)? If so, which one? If not to either of the above, then please explain where the text is and how it got there.

Dave Punky · Sep 24, 2011

Thanks for your replies!

@ Rick

The data is copied into clipboard from a .txt file and then pasted into a UserForm textbox on my sheet which supports multiline. So it's essentially one single string of text when it is pasted into the userform. It is a standard return/enter to break each line, so I guess this is classed as Windows NewLine?

Dave Punky · Sep 26, 2011

Hi all,

Ok so maybe I'm going completely wrong here but I keep getting a subscript error when trying to use the method AlphaFrog has suggested.

Here's exactly how I've coded it:

Code:

Private Sub CommandButton1_Click()
Dim vLines As Variant, vEntries As Variant
Dim ref1 As String, ref2 As String
Dim newliner As Integer
Dim i As Integer
newliner = 1
 
'Split data by lines
vLines = Split(Me.tbwheretextis.value, vbCrLf)
For i = LBound(vLines) To UBound(vLines)
'Split lines by entries
vEntries = Split(vLines(i), vbTab)
'Test third entry (base zero so third entry is index #2)
ref1 = CStr(vEntries(2))
ref2 = CStr(vEntries(3))
 
Sheets("test").Range("A" & newliner).Value = ref1
Sheets("test").Range("B" & newliner).Value = ref2
newliner = newliner + 1
 
Next i
 
End Sub

I'm not really sure where it's going wrong...

AlphaFrog · Sep 26, 2011

Private Sub CommandButton1_Click() Dim vLines As Variant, vEntries As Variant Dim i As Long With Sheets("Test") 'Split data by lines vLines = Split(Me.tbwheretextis.Value, vbCrLf) For i = LBound(vLines) To UBound(vLines) 'Split lines by entries vEntries = Split(vLines(i), vbTab) If UBound(vEntries) >= 2 Then .Range("A" & i + 1).Value = CStr(vEntries(2)) Else .Range("A" & i + 1).Value = "N/A" End If If UBound(vEntries) >= 3 Then .Range("B" & i + 1).Value = CStr(vEntries(3)) Else .Range("B" & i + 1).Value = "N/A" End If Next i End With End Sub

Dave Punky · Sep 27, 2011

That did the job, thanks a lot AlphaFrog!

Extracting Data from long string

Dave Punky

Board Regular

Excel Facts

AlphaFrog

MrExcel MVP

Rick Rothstein

MrExcel MVP

Dave Punky

Board Regular

Dave Punky

Board Regular

AlphaFrog

MrExcel MVP

Dave Punky

Board Regular

Similar threads

Forum statistics

Share this page

Extracting Data from long string

Dave Punky

Board Regular

Excel Facts

AlphaFrog

MrExcel MVP

Rick Rothstein

MrExcel MVP

Dave Punky

Board Regular

Dave Punky

Board Regular

AlphaFrog

MrExcel MVP

Dave Punky

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock