Split Cell data into rows

[BS Singh]

Hi Experts,

I Please go through the attached spreadsheet. I have data in column A and Column E & F shows the required output.

Many thanks for your help.
Regards,
BS

[Kaper]

Nice approach would probably use regular expressions, but "quick and dirty" can be based on series of split functions.
starting with something like
sets_of_data_in_a_row = Split(Replace(Cells(currentrow, "B"), " ", ""), "&")
then splitting for , and finally checking (and splitting) for -
Is that enough suggestion, or shall I show sample code?

[patel45]

Please Login or Register  to view this content.

[millz]

Try the attached

[Kaper]

OK, I can see that ready-code not just-directions prevails
And we have two other approaches (string functions and regular expressions covered)
So let me show also my propsition of (quite compact, isn't it) code using cascade splitting:

Please Login or Register  to view this content.

[BS Singh]

All provided solutions are unique and worked as per requirement. How to mark it solved.

Thanks for your help and time.

Regards,
BS

[patel45]

Kaper, very good and compact code, but you assumed only 2 characters before numbers

[Kaper]

Sure, only two, and exactly two.
It follows the sample and ... I know that in many cases it is just like that.
Two_letters_and_number codes are almost as popular as TLA (three-letter acronym) ;-)