Searching through a string for a sub-string

[nbannon]

Hi,

I need to search through a string which is a whole bunch of xml code, for the substring "inititatingid", then return the start position of this sub-string. Any ideas on the VBA function that can do this.

e.g. If the string was "<transaction id>1234<initiatingid>5555<destination>usa", then I would like the function to return 21, as this is the start position of the sub-string "initiatingid".

Any help would be great

Cheers

Noel.

 

[nbannon]

woops, that example won't make sense. I was using xml tags. Revised example...
"transactionid:1234 initiatingid:5555 destination:usa"

for this example, I would like the function to return 20.

Hope that makes more sense

 

[PaddyD]

This could be done in a formula...

unused_paygo_Last6M_23sep02-.xls
	A	B	C	D
1	/transactionid/1234/initiatingid/5555/destination/usa	22
2
3
4
5
Sheet2

Or am I being too simple minded? (changed the< etc in the sheet to / )

Paddy
This message was edited by PaddyD on 2002-09-22 21:50

 

[Ivan F Moala]

Try this;
Look up help on [InStr]


<pre/>
Sub Tester()

Const strVar As String = "transactionid:1234 initiatingid:5555 destination:usa"
Const strSearch As String = "initiatingid"

MsgBox InStr(strVar, strSearch)

End Sub
</pre>

 

[nbannon]

PaddyD,

Is there a VBA function that does the same thing? I will be searching through multiple strings, so I don't really want to be returning them all to the spreadsheet in order to use the formula

Cheers