Extract Colours From String

[jamesjhhill]

Hi All, I hope you can help me.

I have a list of products names which contain the colour of the item and then the description. For example "Redddish Brown Tile". What I want to be able to do is to split the item description into the product colour and the product name. So (column 1) Reddish Brown | (column 2) Tile

I have attached the list and listed the colours on the second sheet.

Any help would be greatly appreciated!

Thanks, James

[AliGW]

Please mock up the results you want manually for 20-30 rows.

[jamesjhhill]

I have just uploaded a second version called Help (Mockup) which has the desired result. Many Thanks!

[torachan]

Now you have shown the easy ones, what do you expect for the hard ones at the end of the list.
torachan.

[jamesjhhill]

Firstly, I'm glad you think the first ones are easy! . . . that give's me hope . I've uploaded a third sheet called 'Help (Mockup + HARD)'. Essentially those 5-10 items at the end have no applicable colour so can just be ignored

[Glenn Kennedy]

Your list of colours must have been downloaded from the web. Each colour had a non-printing paragraph break (CHAR160) after it.

So...

1. Colours, C2, copied down:

=SUBSTITUTE(B2,CHAR(160),"")

Copy and paste values BACK over column B. I have done this for you already.

2. List, B2, copied down:
=IFERROR(LOOKUP(1000,SEARCH(Colours!$B$1:$B$190,A2),Colours!$B$1:$B$190),"")

3. List C2, copied down:
=TRIM(SUBSTITUTE(A2,B2,""))

see file.

[Glenn Kennedy]

Jameshill... if you modify and upload a new sheet, do so in a NEW post inthe SAME thread. Do not retrospectively edit the attachment at Post 1 if you have already received replies. It makes everything so difficult to follow.

I have not yet looked at your newest attachment. I'll wait to see how my first reply pans out.

[jamesjhhill]

Thanks Glenn, that solution works perfectly. Sorry for uploading to original post - I couldn't see a way to attach to the same thread. I'll make sure I do that in future! Thanks aging - it works great!

[AliGW]

No need to copy and paste anything with my solution.

B2:

=LOOKUP(1000,SEARCH(SUBSTITUTE(Colours!$B$1:$B$190,CHAR(160),""),CLEAN(A2)),SUBSTITUTE(Colours!$B$1:$B$190,CHAR(160),""))

C2:

=TRIM(SUBSTITUTE(A2,B2,""))

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.