Data separation from single column multiple columns

Hello Experts..

In the attached excel (sample data.xlsx) - In Column A various object properties are there. In which I need to extract 3 columns - Class, Object,Select equivalent.
For the reference purpose I have highlighted these 3 column in different colors.
kindly some one can provide a macro to do this data extract.

Thanks In Advance,

SP

Hi KOKOSEK,

Thank you for the quick reply.
As per your macro data is splitting into individual columns but some information is coming. If we add a condition to pull the data between two strings then this code would be perfect for requirement.

for Class column values should be in between 2 strings ("Class: " and "Description: " )then result would be "Contract Terms"

For Object column values should be in between 2 strings ("Object:" and "Type:" ) then result would be " Qualifier"

For Select equivalent column values should be in between 2 strings : ("Select equivalent: " and "Where equivalent: " ) then result would be "CONT_CALC.PRICE_GRP_TYP_CALCBASE"

Thanks again.

can you please check it now.

Thanks!!

Hey KOKOSEK,

The given macro is working pretty much as expected. But when i ran the same macro for similar data set , output is missing some data.
I have attached another sample2.xlsx data set, Could you please look the data and modify the macro accordingly.

Thank you very much for all your support

Thanks,

Hey..

just a thought , before generating Definition column,
After identifying object value,
search for which cell having "select equivalent " word and which next cell having "Where equivalent"

after identifying the cell if we can concatenate the cells between "select equivalent and Where equivalent "
then after we can extract data between "select equivalent and Where equivalent " .

Thanks,

Originally Posted by KOKOSEK

In theory. Sometimes next cell in column contains part of value which we are looking for, sometimes indeed word 'Where', sometimes is next cell in column, sometimes 3 row down.

True, Is there anyway to have a nested loop and search for the string?
Any Thoughts?

Thanks,

Try

Please Login or Register  to view this content.

This is perfect

Thanks for the solution.