Split Column Using Another String

Hi All,

i have a scenario where i have a huge list of images which are named after the SKU of products. what i need to do here is find out which image belongs to which SKU. it seems pretty impossible to me. is there any possible way to do this with power query ? i am relatively new to power query and vba.

splitting the column by delimiter or by number of characters are impossible since the SKU itself may contain these delimiters.

i have the image names in a separate table called Data and SKU's in a separate table.

dear experts kindly help me to solve this scenario. i have attached the sample file here. i am using excel 2016. thank you in advance for your time.

View attachment Image Names.xlsx

If I am understanding correctly, create a new column in C on the SKU tab labelled Image. In C2, type =INDEX(DATA[DATA],MATCH([@SKU],DATA[SKU],0)) and copy down.

alansidman said:

If I am understanding correctly, create a new column in C on the SKU tab labelled Image. In C2, type =INDEX(DATA[DATA],MATCH([@SKU],DATA[SKU],0)) and copy down.

Click to expand...

thanks for your reply. but what i am looking is the exact vice versa to the above formula. mean next to each image name i need to get the SKU. i have filled some rows with desired output in the data sheet.

View attachment Image Names.xlsx

In column B of Data Sheet, type =LEFT([@DATA],FIND("(",[@DATA])-4)

alansidman said:

In column B of Data Sheet, type =LEFT([@DATA],FIND("(",[@DATA])-4)

Click to expand...

hi thanks for your time and effort to answer my question. your formula will work fine if all the records have "(" in the string and same interval as "4" Characters from the the "(". but the actual scenario is entirely different and each and every records are unique. for a eg : see this image name

"XP0138XCPTR-White-A CROPPED.jpg" ==> here the above formula returns the #value! error. same way i have hundreds of thousand rows image name with no regular pattern.

the only common thing between the two data set is SKU. which is in another table and i have no way to match as far as i know.

hope i have explained clearly.

I tried to do this job as simply as possible but some steps had to be manually changed.