Extract data from right until blank

[kchbs]

Can someone please help me with a formula to extract a word from a cell starting from the right moving left until blank. I want to put this information into a separate cell.

Here is sample of the data:

Univ. Connecticut Med School Farmington
266 Coldbrook Road Glastonbury
100 Retreat Avenue Hartford
195 S. Main Street Middleton
87 Grandview Avenue Waterbury
87 Grandview Avenue Waterbury




THANKS so much for any assistance

Karen

 

[Aladin Akyurek]

A generic formula to extract last word...

=RIGHT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Simpler and faster if you install the morefunc.xll add-in...

=WMID(A1,WORDCOUNT(A1))

 

[kchbs]

thank you, thank you, thank you!!

One more question - how do I cut the same text I extracted out of the first cell so that now they are split into 2 and there is no duplication of the city

Karen

 

[north19701]

Book8
	A	B	C	D	E
1	Univ.ConnecticutMedSchoolFarmington	Farmington	Univ.ConnecticutMedSchool
2	266ColdbrookRoadGlastonbury	Glastonbury	266ColdbrookRoad
3	100RetreatAvenueHartford	Hartford	100RetreatAvenue
4	195S.MainStreetMiddleton	Middleton	195S.MainStreet
5	87GrandviewAvenueWaterbury	Waterbury	87GrandviewAvenue
6	87GrandviewAvenueWaterbury	Waterbury	87GrandviewAvenue
7
8
9
10
Sheet1

Using Aladin's formula in b1, the formula in c1 is what I used to separate the city out. Surely, Aladin's solution will probably be much cleaner and more efficient, but I just started playing around with text and I thought I'd take a stab at it.

Else, another formula that could go in C1 is:

=LEFT(A1,LEN(A1)-LEN(B1))

 

[Aladin Akyurek]

...

=REPLACE(A1,SEARCH(B1,A1),LEN(A1),"")

...

=LEFT(A1,LEN(A1)-LEN(B1))

You need to wrap TRIM around whichever you choose to apply though.