Left 3

[billandrew]

Good afternoon

I am using the formula below to extract the first 3 words in a cell. Would it be possible to interpret the formula a bit so I can possibly modify- reason, there are some cells which I require to extract the first 4 words.

Thanks Gents....



Extract 3 =LEFT(TRIM(A2),FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",3))-1)

Extract 4 =LEFT(TRIM(A2),FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",4))-1)

 

[Aladin Akyurek]

How do you know it must be 3 or 4?

 

[billandrew]

The data looks like.

Column A - Before	Column B - Results
123 Smith Street Billings Montana	123 Smith Street
345 East 2nd Street Trenton New Jersey	345 East 2nd Street

<tbody>
</tbody>

 

[Aladin Akyurek]

Thanks, but do we decide it's 3 or 4? Or is it always up to "street"?

 

[billandrew]

It could be 3 words or 4. It could be street, road or ave.

Thank you

 

[Aladin Akyurek]

It could be 3 words or 4. It could be street, road or ave.

Thank you

I guess I fail to pose the right question. How do we decide it's 3 or 4 or street or road or ave.?

Maybe:

Book1
	A	B	C
1	input	output	ave.
2	123 Smith Street Billings Montana	123 Smith Street	avenue
3	345 East 2nd Street Trenton New Jersey	345 East 2nd Street	road
4	42nd street	42nd street	street
5	park road	park road
Sheet1

In B2 enter and copy down:

=LEFT(A2,LOOKUP(9.99999999999999E+307,SEARCH($C$1:$C$4,A2))+LEN(LOOKUP(9.99999999999999E+307,SEARCH($C$1:$C$4,A2),$C$1:$C$4))-1)


Note that C1:C4 are relevant words and they are sorted in ascending order.


Does this help?

 

[billandrew]

Aladin

Amazing....

I actually did not want to utilize a helper column, however I also have "Place" "blvd" & most likely others I missed. I've adjusted the Search Range to my need. Works great!

Thank You

Could you explain a bit

 

[Aladin Akyurek]

Aladin

Amazing....

I actually did not want to utilize a helper column, however I also have "Place" "blvd" & most likely others I missed. I've adjusted the Search Range to my need. Works great!

Thank You

Could you explain a bit

The range for the key words to look for is not a helper column in the usual sense of this designation.

Note that after any addition the range must be sorted again in ascending order.

Explanation:

1. The LOOKUP(9.99999999999999E+307,SEARCH($C$1:$C$4,A2)) bit returns a position in A2 where a key word starts (the position is an integer). This LOOKUP idiom always return the last or the only number from the reference it looks at. Note that SEARCH returns a reference (an array) of numbers (positions) hen successful and #VALUE errors otherwise.

2. The LOOKUP(9.99999999999999E+307,SEARCH($C$1:$C$4,A2),$C$1:$C$4) bit returns the key word itself if successful. LEN determines the length of the key word.

3. The LEFT bit, that is,

LEFT(A2, position of the key word + length of the key word minus 1)

delivers the result we are looking for.

 

[billandrew]

Thank You very much

 

[Aladin Akyurek]

Thank You very much

You are welcome.