I'm trying to extract specific data from rows that are not always in the same position. The data I'm interested in is divided into 3 parts and separated by periods ".". Some data only contains 2 parts. Each data field is preceeded and followed by a space, such as;
ABC , 123-All- , Fixed , Help.Me.FindThis , Or Else , Default , Default (data is in 4th position)
DEF , Fixed , Help.Me.GetThis , Or Else , Default , Default (data is in 3rd position)
X , 123-All- , Broken , Beautiful , Get.This.Data , Or Else , Default , Default (data is in 5th position)
ABC , 123-All- , Broken , Careful , Get.This. , Easy as pie , Hard as nails , Bye (data is in 5th position and 3rd field is blank)
There's actually 5 or 6 different positions the required data would be in.
And each data would be separated into 3 new fields, Field1, Field2, and Field3.
So, my results would be;
Field1
Field2
Field3
Help
Me
FindThis
Help
Me
GetThis
Get
This
Data
Get
This
I've been trying nested IF's using TextBetween and have gotten good results, but still can't get 100% of the data extracted, and if some new "IF" came along I'd have to redo the nested IF's. I was hoping someone else has run across this and has a better mouse trap. Perhaps using SubField?
Message was edited by: johncaqc
Fixed data in table
A combination of both yours and Rob's suggested solutions helped, but when I applied the rules to the full dataset I had even more instances where the data had more concatenated segments, and these rules returned them as well. What I ended up doing is using my original nested IF statements, and of the 7,000+ rows it took care of all but 10 of them. I'm fairly certain new entries will cause this to grow, but my hope is the increase is negligable. My customer is happy with the results thus far, and that is the bottom line. If I get more time I'll play with these rules more, or post the additional rows with extra segments.
All help has been appreciated...I've learned some new tricks.
