Extracting Address Unit Number From the Property Legal Description

[Castor305]

Hello All,

I have the following cells in my excel sheet:

I would like to extract the "Unit Number" from the Legal Description and then combine the whole address in one cell. End product should look like this:

The main issue i'm having is extracting the Unit Number from the Legal Description.

I would truly appreciate if someone could help me...Thank you!!

 

[bebo021999]

Book1
	A	B	C	D	E	F	G
1
2
3	270 NW 71 Avenue	Laguna Condo Unit 7 Unidiv	Miami	FL.	33126		270 NW 71 Avenue Unit 7 Miami, FL 33126
4	270 NW 71 Avenue	Unit 104 xxx Unidiv	Miami	FL.	33126		270 NW 71 Avenue Unit 104 Miami, FL 33126
5	270 NW 71 Avenue	xxxxx Unit 560	Miami	FL.	33126		270 NW 71 Avenue Unit 560 Miami, FL 33126
Sheet2

Cell Formulas
Range	Formula
G3:G5	G3	=A3&" "&TRIM(LEFT(SUBSTITUTE(MID(" "&B3&" ",SEARCH(" Unit "," "&B3&" "),255)," ",REPT(" ",100)),300))&" "&C3&", "&SUBSTITUTE(D3,".","")&" "&E3