Address problem when VBA'ing my data

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
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
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
=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:
AB
1PO BOX 11111 PO BOX 11111PO BOX 11111
2MIAMI FL 33233 MIAMI FL 33233MIAMI FL 33233
3ATTN: ACCOUNTS PAYABLE ATTN: ACCOUNTS PAYABLEATTN: ACCOUNTS PAYABLE
4JUPITER FL 33478 JUPITER FL 33478JUPITER FL 33478
512345 123RD ST. 98765 PAYER LANE12345 123RD ST. 98765 PAYER LANE
6JUPITER FL 33478 JUPITER FL 33478JUPITER FL 33478

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

Worksheet Formulas
CellFormula
B1=IF(TRIM(LEFT(A1,LEN(A1)/2))=TRIM(RIGHT(A1,LEN(A1)/2)),TRIM(LEFT(A1,LEN(A1)/2)),A1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
That is a great formula, the evaluation of it made perfect sense. Thank you for helping.
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,294
Messages
5,745,936
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top