Extract cells if they have 2 words (or one space)

Hi, Excel begginer here. I solved this like a month ago and now I can't remember how I did it... LUL

I have a column with text and need a formula to find and extract the cells that have just two words (or one space). Like this:

excel1.PNG

Thank you in advanced.

One way, using an array formula:

=IFERROR(TRIM(INDEX(A:A,SMALL(IF(LEN(TRIM($A$1:$A$7))-LEN(TRIM(SUBSTITUTE($A$1:$A$7," ","")))=1,ROW($A$1:$A$7)),ROWS(C$1:C1)))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Hey Glenn, thanks for the answer. That's right, an array formula.

I can't make it work with my original data.

Please Login or Register  to view this content.

are the columns right? So if I have 1000 columns it should be

Please Login or Register  to view this content.

.


For reference this is my orginal file: https://drive.google.com/open?id=0Bw...015UVUxQkVkX2c
And the solution I did back in July: https://drive.google.com/open?id=0Bw...TFENmlOdlJJUGs

What's the full formula that you are using... and in which cell did you enter it? are you sure that the array was set correctly? Did you see the { ... } around the formula in the formula bar?

I am roaming on a slow metered connection and did not / will not download a 66 Mb file!!

Originally Posted by shivya

=if(len(m7)-len(substitute(m7:m7," ",""))+1=2,m7:m7," ")

That doesn't remove blank cells (see Post 1) and doesn't work if there is inadvertantly an additional space (or several spaces) between words...

So... what are you seeing? Put the cursor in C1 (so I can see the formula in the formula bar) and post a screenshot (usually I hate screenshots...)

You're in Spain. I didn't notice. Did you change the , into ; ?

Normally, I'm in Ireland, but right now I'm in the middle of the Ebro delta...

Array formulae don't work in CSV files (I'm 95% certain). The formula results convert to values, I think. Can't you save as .xlsx???

In your .xlsx, you had not changed the range from rows 1-7 to 1-120....

=IFERROR(TRIM(INDEX(A:A,SMALL(IF(LEN(TRIM($A$1:$A$120))-LEN(TRIM(SUBSTITUTE($A$1:$A$120," ","")))=1,ROW($A$1:$A$120)),ROWS(C$1:C1)))),"")

It works fine...

No reason.... other than that you can make the range a bit longer, if additional values will be added.

Anyhow...You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.