Consolidating multiple different text values into fewer values (IF cells contain X, then…)

I am consolidating a list that contains several values in a column into fever values in another column.

Basically there is in column A
Region 01 – bla bla bla
Region 01 – bla bla
Region 01 – bla
Region 02 – bla bla bla
Region 02 – bla bla
Region 02 – bla
…

What I want is to put in the same row, column B
Region North (for all regions containing Region 01)
Region South (for all regions containing Region 01)

Happy for solutions. Thanks.

If I understood your request correctly, in B2, try this:

=LEFT(MID(A2,FIND("-",A2)+2,LEN(A2)),FIND(",",MID(A2,FIND("-",A2)+2,LEN(A2)))-1)

thx – this actually returns an "The formula you typed contains an error." I am using Excel for Mac. I am a vary basic, occasional excel user and don't understand the working of this formula at all.

If you upload the actual worksheet (instead of just a picture of it), I will input the formula and then upload the worksheet with the formula applied.

It is possible that our regional settings are different which is causing the error.

I will put a simpler formula on there as well.

wow – super helpful!

Here are 3 formulas that return "North West", "East", "West", "Mid", "South West", or "South East".

this works perfectly –*thanks a million.

You're welcome, happy to help. Thanks for the rep!

If that solved your question, please mark this thread as SOLVED.

done, and will do in the future. More questions to come!