I am having a problem working a formula to get the data I need. I have attached a test spreadsheet.
The raw data comes in on column A and I need the values between the <value> and </value> text for each cell. There can be up to 5 <value> and </value> entries in each cell of raw data so I need up to 5 columns of values for each raw data entry.
My approach -
Figure out how many value and /value entries were in each cell of data which is my column G
Then figure out in which potential columns there was value and /value data in - For instance in cell A9 there is data between the values for Invoiced and Hours so I need blank cells in R9, T9, and V9, 20 in cell S9, and 86 in cell U9.
So my idea was to get find the location of the first <value> and first </value> by using Find in M9 and N9 and then I was going to attempt to use Left or some combo using left, right, mid, ect to get the 20. However 20 is 2 characters and there is a 10 character difference in the values in M9 and N9 and the entries will never have a fixed number of characters it could be anything between 2 and 40 characters.
I hope I have made it somewhat clear of what I need help with. I know my skill level and I will need help with how to extract the first 2 potential value and /value combos, from there I am sure I can figure out how to get the value in the remaining combos if there is data to extract.
Thank you in advance.