CODEWITHSUNDEEP
vbaexcel
user1753675
user1753675

Reputation: 145

Getting the nth row of a string

I have the following string:

BAXH14#cote href="nego_cotes_in_en.php?symbol=BAX&amp;instrument=BAXH14#cote">14 MAR</A></TD>
<TD>98.620</TD>
<TD>98.630</TD>
<TD>98.620</TD>
<TD>0</TD>
<TD>33,568</TD>
<TD>6,071</TD><

I would like to retrieve the 3rd row as in 98.620. The numbers in this string will change everyday so any help would be great.

Upvotes: 1

Views: 729

Answers (4)

InContext
InContext

Reputation: 2501

most straightforward way if it is all one string and not a file you are parsing or across multiple rows is to use the following. Assumes string is in Range("A1") of Sheet1.

Dim dataSplit() As String
Dim returnData As String

dataSplit = Split(Sheet1.Range("A1").Value2, "</TD>")

returnData = Right$(dataSplit(2), Len(dataSplit(2)) - 5)

Upvotes: 6

James Snell
James Snell

Reputation: 603

A simple way is to break up the string into an array using the Split function. This takes a string and prints the 3rd item in the debug window. Like this...

Sub split_td()
    Dim values() As String
    Dim sourceString As String
    Dim result As String

    ' get your source data here
    sourceString = "<td>1</td><td>2</td><td>3 - pick me!</td><td>4</td>"

    'break it into an array of strings
    values = Split(sourceString, "<td>")

    'tidy up the end tag
    result = Replace(values(3), "</td>", "")

    Debug.Print values(3)
End Sub

If you don't mind an extra column in your sheet then you can do something like this...

A------------------------     B----------------------------------------------------------

                              <td>1</td><td>2</td><td>3 - pick me!</td><td>4</td>
=SEARCH("<td>", B$1)    
=SEARCH("</td>",B$1)        =SUBSTITUTE(SUBSTITUTE(MID(B$1,A2,A3-A2),"/",""),"<td>","")
=SEARCH("</td>",B$1,A3+1)       =SUBSTITUTE(SUBSTITUTE(MID(B$1,A3,A4-A3),"/",""),"<td>","")
=SEARCH("</td>",B$1,A4+1)       =SUBSTITUTE(SUBSTITUTE(MID(B$1,A4,A5-A4),"/",""),"<td>","")
(etc)

Which then puts the points for the text operations in column A and does the extraction in column B.

Upvotes: 0

Rick
Rick

Reputation: 1073

Assuming you are using VBA this will do the trick - just set str to your string

Sub test()
Dim str As String
Dim n As Integer

n = 3

str = "testData<TD>98.620</TD><TD>98.630</TD><TD>98.620</TD><TD>0</TD><TD>33,568</TD><TD>6,071</TD>"
subSTR = str
For i = 1 To n
    If Not (InStr(1, subSTR, "<TD>") = 0) Then
        subSTR = Right(subSTR, Len(subSTR) - InStr(1, subSTR, "<TD>") - 3)
    Else
        subSTR = ""
        Exit Sub
    End If
Next i

finalStr = Left(subSTR, InStr(1, subSTR, "</TD>") - 1)

End Sub

Upvotes: 1

Lord Peter
Lord Peter

Reputation: 3501

Using Mid (string, start, length) and find (what, where, startpos) you can skip two line feeds - char (10) and set length to the difference between the second and third line feed position like this:-

=MID(A1,FIND(CHAR(10),A1,FIND(CHAR(10),A1,1)+1),FIND(CHAR(10),A1,FIND(CHAR(10),A1,FIND(CHAR(10),A1,1)+1)+1)-FIND(CHAR(10),A1,FIND(CHAR(10),A1,1)+1))

Where A1 is assumed to contain your string. If you need to treat as end of line marker then replace all the char (10) with "".

Elegant? Of course not!

Upvotes: 1

Related Questions