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:

713 Arch Street, Jacksonville Beach, FL 32250 341 Cardinal Drive, Wethersfield, CT 06109 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. <colgroup><col width="363" style="width:272pt"> </colgroup><tbody> </tbody> <colgroup><col width="363" style="width:272pt"> </colgroup><tbody> </tbody>

<colgroup><col width="363" style="width:272pt"> </colgroup><tbody>
</tbody>

 

[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
1			Street address	City	State	ZIP
2	713 Arch Street, Jacksonville Beach, FL 32250		713 Arch Street	Jacksonville Beach	FL	32250
3	341 Cardinal Drive, Wethersfield, CT 06109		341 Cardinal Drive	Wethersfield	CT	06109
4	134 Main Street East, Anchorage, AK 99504		134 Main Street East	Anchorage	AK	99504

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C2 =LEFT(A2,FIND(",",A2)-1) D2 =TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(C2)+2,LEN(A2)),",",REPT(" ",LEN(A2))),LEN(A2))) E2 =MID(A2,LEN(A2)-7,2) F2 =RIGHT(A2,5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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]

Worksheet Formulas Cell Formula D2 =TRIM(LEFT(SUBSTITUTE(MID(A2,LEN(C2)+2,LEN(A2)),",",REPT(" ",LEN(A2))),LEN(A2))) <tbody> </tbody>

<tbody>
</tbody>

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
1	Item	Price	Date	Time		Items sold after 3 PM
2	Pencil	$ 1.00	1-Jan	11:00:00 AM		3
3	Pen	$ 1.95	1-Apr	1:49:00 PM
4	Notebook	$ 2.50	6-May	3:01:00 PM
5	Mouse	$ 12.95	21-Jun	5:45:00 PM
6	Car	$ 10,000.87	23-Jun	9:21:00 PM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =COUNTIF(D:D,">"&TIMEVALUE("3:00 PM")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.