Left 3

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
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)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
The data looks like.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A - Before[/TD]
[TD]Column B - Results[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123 Smith Street Billings Montana[/TD]
[TD]123 Smith Street[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]345 East 2nd Street Trenton New Jersey[/TD]
[TD]345 East 2nd Street[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
ABC
1inputoutputave.
2123 Smith Street Billings Montana123 Smith Streetavenue
3345 East 2nd Street Trenton New Jersey345 East 2nd Streetroad
442nd street42nd streetstreet
5park roadpark 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?
 
Upvote 0
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:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top