Remove City and US state codes from an address string

[Bi Yoro Olivier ZOUZOU]

Hi all,

I am new in this forum and also new in my programming career in spreadsheets. I have a thousands of US addresses strings from which I have to separate the home number and street name, the city, the State code, and then the postal code.
Here are three examples:

[TABLE="width: 363"]
<colgroup><col width="363" style="width:272pt"> </colgroup><tbody>[TR]
[TD="class: xl16, width: 363"]713 Arch Street, Jacksonville Beach, FL 32250
[TABLE="width: 363"]
<colgroup><col width="363" style="width:272pt"> </colgroup><tbody>[TR]
[TD="class: xl16, width: 363"]341 Cardinal Drive, Wethersfield, CT 06109
[TABLE="width: 363"]
<colgroup><col width="363" style="width:272pt"> </colgroup><tbody>[TR]
[TD="class: xl16, width: 363"]134 Main Street East, Anchorage, AK 99504

a) For the Home number and street name, I'm ok with this formula: =MID(A6,1, FIND(",", A6,1)-1)

But I cannot find the formula to remove the cities and the postal codes.


Please help me

Regards,Olivier.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

These kind of problems are notorious for being difficult due to variations in the input data. If all your data looks like your examples, these might work:

	A	B	C	D	E	F
Street address	City	State	ZIP
713 Arch Street, Jacksonville Beach, FL 32250	713 Arch Street	Jacksonville Beach	FL
341 Cardinal Drive, Wethersfield, CT 06109	341 Cardinal Drive	Wethersfield	CT
134 Main Street East, Anchorage, AK 99504	134 Main Street East	Anchorage	AK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]32250[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]06109[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]99504[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=LEFT(A2,FIND(",",A2)-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(C2)+2,LEN(A2)),",",REPT(" ",LEN(A2))),LEN(A2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=MID(A2,LEN(A2)-7,2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=RIGHT(A2,5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Any slight variations could throw them off though.

Hope this helps.

 

[Bi Yoro Olivier ZOUZOU]

Hi Eric,

Thank you very much for your help. The second formula doesn't work. Please, help me to fix the problem
=TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(C2)+2,LEN(A2)),",",REPT(" ",LEN(A2))),LEN(A2)))

Best,

Olivier

 

[Bi Yoro Olivier ZOUZOU]

Sir Eric,

I'm sorry the formula works perfectly. I did a wrong entry. I put in my formula LEN(C2)+2 instead of LEN(B2)+2.

Thank you very much for you suport

Olivier

 

[Rick Rothstein]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(C2)+2,LEN(A2)),",",REPT(" ",LEN(A2))),LEN(A2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Since there will be calculated values in Columns C, E and F, you should also be able to use this simpler formula in cell D2...

=MID(A2,LEN(C2)+3,LEN(A2)-LEN(C2)-12)

Same caution about any variations in the structure of the inputted values as you posted.

 

[Eric W]

Bi Yoro Olivier ZOUZOU: Glad we could help!

Rick: Good point! I worked out the formulas from left to right. I should have gone back to simplify D2.

 

[Bi Yoro Olivier ZOUZOU]

Hello Everyone,

Please, I need help with this SUMIF FUNCTION. I have a spreadsheet with 1000 items sold in column A and the hour of sale in column D. I am asked to find the total number of items sold after 3PM.In column B I have the price of each item and in column C I have the date of sale of the corresponding item.

I used the following formula: = =SUMIF(A:A, ">3PM",D:D)=549.62

I found a result with decimal. However, there is no decimal in the number of items sold. Since column A (1000 items) represents the items sold. from a part has been sold after 3PM.

Help, please

Olivier

 

[Eric W]

For future reference, if you have a significantly different question, you should open a new topic.

Next, you want COUNTIF:

	A	B	C	D	E	F
Item	Price	Date	Time	Items sold after 3 PM
Pencil
Pen
Notebook
Mouse
Car

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"] $ 1.00 [/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"] $ 1.95 [/TD]
[TD="align: right"]1-Apr[/TD]
[TD="align: right"]1:49:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] $ 2.50 [/TD]
[TD="align: right"]6-May[/TD]
[TD="align: right"]3:01:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] $ 12.95 [/TD]
[TD="align: right"]21-Jun[/TD]
[TD="align: right"]5:45:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] $ 10,000.87 [/TD]
[TD="align: right"]23-Jun[/TD]
[TD="align: right"]9:21:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=COUNTIF(D:D,">"&TIMEVALUE("3:00 PM"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If the time values in D are stored as Excel times, and not text, then Excel stores them as a fraction. .25 = 6 AM, .75 = 6 PM, etc. You can use .625 in the formula for 3 PM if you like, but using TIMEVALUE (or TIME) makes it much more readable.