Pulling data from between apostrophes ?

[bdsii]

Any help would be appreciated. I am uploading a sample file to help explain the data. The data is in Column A and for the first set of data, the results are shown in Column B.

All data is in one column on one sheet. I want to pull data from between apostrophes from specific rows. Those specific rows are based on the text at the beginning of the cell and seems to remain constant throughout the sheet.

The data for the first line I want appears like the following complete with preceeding spaces:
this.number = '1';
I want to pull the Number 1 and put that data in Column B

The next line appears as below:
this.name = 'TIDECLEAN #7';
I want to pull TIDECLEAN #7 and put that is Column B.

There are several lines that I would like to pull data as demonstrated from above. The spaces and the text preceeding the apostrophes repeat in the list with different data that needs to be extracted.
this.number = '1'; this.name = 'TIDECLEAN #7'; this.address = '6651 WALL TRIANA'; this.city = 'MADISON'; this.county = 'MADISON'; this.stateProvince = 'AL'; this.postalCode = '35757-7402'; this.country = 'US'; this.recordId = '208240'; this.latitude = '34.777110'; this.longitude = '-86.749300';

These lines are always in this order. I was thinking of a loop through the rows of the spreadsheet checking for this.number = '
and then someway pulling the text into column B and then repeating that for each line above all the way to the end of the spreadsheet. Once it is in this format I can then put the data into a format that can be used.

The number of characters between the apostrophes will vary.

Anyone know of a quick and easy way to handle this ? I suppose it would require a loop searching for specific text but I do not know the code to get data from between the two apostrophes. Actually they may be helpfu since it will signify the beginning and ending of the text to be captured.

Any advice or help would be appreciated :think:

Thanks!

[bdsii]

Looks like my preceeding spaces before the data is not showing but you can see them in the sample test file I provided. I am not sure if the preceeding spaces would cause a problem with the code or not.

[mdmackillop]

Using a UDF

[p45cal]

Since there are only a very few lines with more than 2 apostrophes in (those that do have more thgan two I doubt you're interested in) you can use Data|Text To Columns, using the apostrophe as the delimiter, and only import the second column (skipping the 1st and 3rd). It gave the right results here.

[bdsii]

mdmackillop - thanks for the spreadsheet - I have learned something new with this and will have to do more research on the UDF. It works great!



p45cal - thanks for the info. My preference is to only have data I want in column B so the text to columns would work but I would have to filter through it for the items I want. I had forgotten the text to column option though...will play around with it too.

I appreciate it!

[GTO]

Greetings,

If I am 'getting' which records to pull, maybe:

Option Explicit

Sub main()
Dim _
wksData As Worksheet, _
rngRecords As Range, _
rngEnd As Range, _
aryRecordRanges As Variant, _
i As Long

Const SH_DATA As String = "OrigData(2)" '<Your sheet name

Set wksData = Worksheets(SH_DATA)
With wksData

Set rngRecords = .Range("A:A")
With rngRecords
Set rngEnd = .Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
Set rngRecords = .Range(.Range("A1"), rngEnd)
aryRecordRanges = rngRecords.Value

For i = 1 To UBound(aryRecordRanges, 1)
'this.user5 = 'C-Store, Car Wash, Gift Cards,';
If aryRecordRanges(i, 1) Like "*this.user*=*'*'*" Then
aryRecordRanges(i, 1) = Split(aryRecordRanges(i, 1), "'")(1)
'this.number = '1';
ElseIf aryRecordRanges(i, 1) Like "*this.number = '*" Then
Do While aryRecordRanges(i, 1) Like "*'*'*"
aryRecordRanges(i, 1) = Split(aryRecordRanges(i, 1), "'")(1)
i = i + 1
Loop
i = i - 1
Else: aryRecordRanges(i, 1) = vbNullString
End If
Next

rngRecords.Offset(, 1).Value = aryRecordRanges
End With
End With
End Sub


Mark