Searching through a string for a sub-string

nbannon

New Member
Joined
Aug 4, 2002
Messages
23
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

nbannon

New Member
Joined
Aug 4, 2002
Messages
23
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
This could be done in a formula...
unused_paygo_Last6M_23sep02-.xls
ABCD
1/transactionid/1234/initiatingid/5555/destination/usa22
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

New Member
Joined
Aug 4, 2002
Messages
23
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top