only two words to show

[hendrikbez]

I have a sheet with 4163 rows in one column

I want to put some words in new column (only the ones with two words)

eg.

1 house;flat
2 he is not home;man
3 where is it;here
4 woman;child
5 son;moon
6 to help me;wife

So I only want the ones with 2 words like numbers 1,4 and 5

[jeffreybrown]

How about in B1 copied down...

=IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),A1,"")

[hendrikbez]

I have type this in =IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),A1,"") in column b row 1, but it is doing nothing

Attachment 643120

[jeffreybrown]

Hi hendrikbez,

A picture does not help.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need.

Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data and make sure you have annotated what you expect.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AlKey]

Maybe this?
Enter formula in B1 and copy down

=(LEN(A1)=LEN(SUBSTITUTE(A1," ","")))+0

v	A	B
1	house;flat	1
2	he is not home;man	0
3	where is it;here	0
4	woman;child	1
5	son;moon	1
6	to help me;wife	0

[hendrikbez]

Hi

I have make a before and after sheet.

The words are split by ;

Sheet before is all the words in the sheet

sheet after is where the words with only 2 word is moved to column b and all the others stay in column a

[jeffreybrown]

Hi hendrikbez,

How about this which uses the formula in column B, but also conditional formatting in column A.

[AlKey]

Is this?

=IFERROR(INDEX(A:A,IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),ROWS(A$1:A1),"")),"")

v	A	B
1	Aan die kant;Enersydse
2	Aanhoudend vra;Soebat
3	Aanhoudende suising;Geruis
4	Aanstaande;Inspe	Aanstaande;Inspe
5	Aanteken;Annoteer	Aanteken;Annoteer
6	Aaron was Moses s'n;Segsman
7	Abelmos;Okra	Abelmos;Okra
8	Afstand tussen twee ruspunte (Fr);etappe
9	Afsterf;Oorly	Afsterf;Oorly
10	Amp van verteenwoordiger;Agentuur
11	Asemhalings gaatjies by diere;Spirakels
12	Baard se moses (meerv);Skeermesse…
13	Baie dun vesel of draad;Filament
14	Baie;Alte	Baie;Alte
15	Bakterie;Spiril	Bakterie;Spiril
16	Beijing word so genoem;verbodestad
17	Bekend vir sy mosterd;Dijon
18	Bekende Kaapse stadsplein;parade
19	Bekende sprokieskarakter;Aspoestertjie
20	Bekende tuinblom;Roos
21	Bekyk;Betrag	Bekyk;Betrag
22	Belangrikste;Kardinaal	Belangrikste;Kardinaal

[hendrikbez]

jeffreybrown and alkey, thank you both of them is working

[jeffreybrown]

You are very welcome & thanks for the feedback. We are happy to help!