Extract Words for a cell

Hi

Could someone please help.

Is there a way to get them two sentences to shows just the words Small in one cell then Natural Wood in another cell
And then Large in one cell then natural wood in another cell?

Would you like your pegs painted?:No all natural wood please | Size:Small 3 x Peg - £45
Would you like your pegs painted?:No all natural wood please | Size:Large 5 x Peg - £105

I can only get it to say Small 3 x Peg - £45

Many Thanks

You can get the size using this formula in C2:

=MID(A2,FIND("Size",A2)+5,5)

then copy down.

You will need to give us some more examples of your data to show some variation if the answer to the question is "Yes" - how do you show the colour in these case?

Hope this helps.

Pete

This is the full piece in trying to organise, just thinking if i can get the above working for now that would help.

Im not 100% if i have uploaded the correct file type.

Change the formula in C2 to this:

=IFERROR(MID(A2,FIND("Size",A2)+5,6),"")

then copy down by double-clicking the fill handle (the small black square in the bottom-right corner of the cursor, with C2 selected).

Hope this helps.

Pete

oh wow they seem to work great


thank you so much

The data in column A seems too variable to be able to extract the colour reliably.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Im trying to figure out how this formula actually works.... is there a way to edit it as i please?

for example

Would you like your pegs painted?:No all natural wood please | Size:Small 3 x Peg - £45

I would only like it to pick up Natural Wood.

Or will it require a totally new formula?

i am now, yes.

i was just wondering if that formula was easily editable.

No worries if not.

It might be possible to list all your colours (plus "natural wood") in a column somewhere and then you could look to see if any of the colours can be found with the text in column A and return that colour to column E.

Hope this helps.

Pete

that might be a shout. cheers

OK, this doesn't quite work where you have multiple colours in the same cell in column A (see below), but I put this list of colours in column H from H1 down:

Natural wood
Light Grey
Dusty Pink
Lavender
Light Blue
Yellow
Orange
Blue
Teal
Graphite
Bright Pink
Sage
Olive
Rust
White

which I obtained manually by examining what you had in the cells in column A. Then you can use this array* formula in cell E2:

=IFERROR(INDEX($H$1:$H$15,MAX(IF(ISNUMBER(SEARCH($H$1:$H$15,A2)),ROW($H$1:$H$15),-1))),"")

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter, rather than the usual Enter.

This can then be copied down as required.

Note that it only picks out one of the colours shown in cell A4 - actually, it is the colour which is furthest down the list, because of the MAX function.

Hope this helps.

Pete

P.S. I see that your profile is out of date, as you would not be able to get the earlier formulae working in Excel 2003, so please update it in your User CP.