Address problem when VBA'ing my data

[hitbid]

I ran into an issue, that may or may not be resolvable.
My client has a "Bill to:" and a "Ship to:" in their raw data.
However the address data is always combined on the same line.
For example, here is some raw data from the sheet. Bear in mind this is all converted from .txt to .xls, and this data is all located in Column A.

PO BOX 11111 PO BOX 11111 - So here, I am supposed to figure out how to separate these identical PO Boxes
MIAMI FL 33233 MIAMI FL 33233 - Here, same thing, separate the identical city/state/zip s from one another.


ATTN: ACCOUNTS PAYABLE ATTN: ACCOUNTS PAYABLE - same, separate the identical data (sometimes a non-address comes in)
JUPITER FL 33478 JUPITER FL 33478 - separate identicals


12345 123RD ST. 98765 PAYER LANE - But not all the data is identical. I haven't tried figuring out how to separate the identical, but when it differs like this, I'm completely lost. There is no rule, no set space or character in column A that differentiates. You and I know that the next address starts at 98765, but how could code possibly know?
JUPITER FL 33478 JUPITER FL 33478


I dont really think it is possible. Right now, all I can do is provide them with one column, that has everything combined, and separated with a comma that I inserted.
They just get one column that looks like this:
PO BOX 11111 PO BOX 11111, MIAMI FL 33233 MIAMI FL 33233
ATTN: ACCOUNTS PAYABLE ATTN: ACCOUNTS PAYABLE, JUPITER FL 33478 JUPITER FL 33478
12345 123RD ST. 98765 PAYER LANE, JUPITER FL 33478 JUPITER FL 33478

I don't know, if you have ideas cool, if not cool as well.

Thanks

 

[shknbk2]

I agree with you that I'm stumped on the non-identical ones, but you could make the identical ones shorter at least. This code returns only the common address once if they are the same, otherwise it returns the whole cell with the different addresses.

=IF(TRIM(LEFT(A1,LEN(A1)/2))=TRIM(RIGHT(A1,LEN(A1)/2)),TRIM(LEFT(A1,LEN(A1)/2)),A1)

Your data could then look something like this:

	A	B
PO BOX 11111 PO BOX 11111	PO BOX 11111
MIAMI FL 33233 MIAMI FL 33233	MIAMI FL 33233
ATTN: ACCOUNTS PAYABLE ATTN: ACCOUNTS PAYABLE	ATTN: ACCOUNTS PAYABLE
JUPITER FL 33478 JUPITER FL 33478	JUPITER FL 33478
12345 123RD ST. 98765 PAYER LANE	12345 123RD ST. 98765 PAYER LANE
JUPITER FL 33478 JUPITER FL 33478	JUPITER FL 33478

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

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

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

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(TRIM(LEFT(A1,LEN(A1)/2))=TRIM(RIGHT(A1,LEN(A1)/2)),TRIM(LEFT(A1,LEN(A1)/2)),A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[hitbid]

That is a great formula, the evaluation of it made perfect sense. Thank you for helping.

 

[shknbk2]

Glad to help.

Just for fun, for the non-identical ones, I thought about running various attempts of different sections of the address through a USPS lookup to check for correct addresses trying to match what was entered compared with what was returned, but I couldn't find a quick way to do that on the USPS site. Manually works fine, but coding might take a bit and running that might take a bit of time in interfacing with the site anyway.