How to extract a substrings if length is not known

[Dan Carlson]

Hi Gang,
I have to deal with a lot of xml report files that all have a header string and my problem is how to parse the string for the nuggets i require for my macro.
This is a sample string:
<Function IDREF="TST_RxRccsMatrix_Rx64" Start="2011-04-07T14:21:35.593000+02:00" Status="Success" Tags="SystemSerialNumber:41009" End="2011-04-07T14:29:16.625000+02:00">

I need to extract
- the report type: TST_RxRccsMatrix (length of this string is not constant)
- the start date-time stamp: 2011-04-07T14:21:35.593000+02:00 (length is constant)
- the serial number: 41009 (length is constant)

I have found methods using Split, InStr and Left but none produce the desired results for all three extractions. The one stumping me is the report type.

I truely appreciate any help on this!

[FDibbins]

I know this is not VBA, it is a regular formula, but maybe you can work around it?

=MID(A1,FIND("""",A1,1)+1,FIND("""",A1,FIND("""",A1,1)+1)-FIND("""",A1,1)-1)
This will give you TST_RxRccsMatrix_Rx64

[Marc L]

Hi,

see also InStrRev and Mid functions …

[TMS]

Try:

Please Login or Register  to view this content.

Regards, TMS

[apo]

Hi..

Can you attach a Workbook with a lot more sample strings.. the more the better so i can see any patterns that exist..

Edit: oops.. I am way to slow in refreshing.. but still attach a Workbook if you like for a VBA solution..

Try this.. assuming your data is in column A.. and you want the results in B,C and D..

Please Login or Register  to view this content.

[jolivanes]

Another one to try (on a copy of your Workbook)
Is there any constant in IDFEF? Maybe Rx64" or the amount of characters in _Rx64" (6 incl underscore) or the word Matrix?

Please Login or Register  to view this content.

Note: apo comes up with some fantastic stuff.