Looping through a string value based on occurrences of a value and parsing out values into new rows

moncdanm

New Member
Joined
May 8, 2019
Messages
1
Hello all,

So I'm trying to do something a little bit complex but am not sure how to tackle it as I'm very new to VBA coding syntax (but intermediately experienced in other programming languages)

Basically, I am trying to parse through this text:

PHP:
<Start>

    <Step>


        <Description>


            This is a description


        </Description>


        <Validation>
    
            This is how we validate
    
        </Validation>


    </Step>


    <Step>


        <Description>


            This is another description.


        </Description>


        <Validation>
    
            And this is another validation.
    
        </Validation>


    </Step>


</End>



And it's parsed based on the key tokens that begin and end the text. So, the beginning statements are brackets followed by the field descriptor and they are thus closed by the brackets and a forward slash.

Right now, I'm using a UDF called Supermid (taken from this website) that returns the values between two given words (in this case, the beginning and end tokens I just described).
So if the text above is in Cell A1, Supermid(A1, "<Validation>","</Validation>") would return "This is how we validate.".

I'm running through a problem where I want to loop through the above text, and parse it seperately for each <Step> instance.

So because there are two <Steps> encapsulating the description token and the validation token, i'd like to seperate them.

So it would look a little something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Step 1[/TD]
[TD]This is a description[/TD]
[TD]This is how we validate[/TD]
[/TR]
[TR]
[TD]Step 2[/TD]
[TD]This is another description[/TD]
[TD]And this is another validation[/TD]
[/TR]
</tbody>[/TABLE]

Essentially I want to loop through the entire field, and each <Step> that is detected is treated as it's own entity that's iterated upon and is parsed out accordingly to respective columns (col B for the description, col C for the Validation)

I'm thinking the pseudo code would look something like this if i'm incorporating supermid and the value I want to parse is in Cell A1:

Code:
For i = 0; i < Steps; i++:
     Range("C1").Formula = "=supermid($A1, ""<Feature Description>"", ""</Feature Description>"")"
     Range("D1").Formula = "=supermid($A1, ""<Feature Validation>"", ""</Feature Validation>"")"]
     Range("B1").Formula = ""Step" & i" //this is to clarify which step number it is


My main issue is converting this psuedo code to VBA syntax, as I've never worked with it to a deep extent and i'm learning as I go.
I'm thinking I have to count the occurrences of <Step>, store that in a Long, and use that as my iterator for 'i'.

The biggest issue is separating the parsed text into new rows for each iteration and I'm not sure how to count the number of Steps that occur in a string value and separate them accordingly.

Is there a way to iterate the Cell number for each loop iteration? And how do I reference that 'i' value when displaying the Steps?

If anyone can point me to the right direction, it would be HUGELY appreciated!

Thank you!!!
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

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
Back
Top