Fix Address field

[Bud2014]

I've been asked to clean up a spreadsheet with 34,000 lines as the addresses are all grouped in one column. Only space separators, some commas, but not enough to use the text to column feature.
I have looked at quite a few of the VBA answers, but I am hopeless lost.

I have tried text to columns with spaces, but it will still require touching everything

Address 1205 Madera Ave Madera 93637 US 3031 Franklin Blvd 95818-3938 US 28200 Wescott Ct 92596 US FAITH BAPTIST TABERNACLE 32696-0000 US 3348 EDGEWOOD AVENUE 33916 US 1218 West St 96021-2943 US 109 S BROAD STREET 34601-0000 US 1210 W ROBINSON ST 32805 US 6910 STIRLING RD 33024 US 18335 Johnson Rd 96080-9467 US 2353 Fifth Ave 94518-1163 US 8510 WEST FLAGLER ST 33144 US

I also tried to use the XL2BB add in with no luck... my skills are limited. Thanks you for any help that can be provided.

 

[Micron]

I'd be surprised if the application of any vba logic resulted in a 100% accurate solution. From what you posted it looks like that logic would be along the lines of

If 1st character is numeric presume that it is the address number. In that case,
- find the first space and assume what lies to the left is the address number
- find 2 spaces from the right and assume what lies between first space and that point is the street info
- get the last 2 characters for the country code if can only be 2 characters, or find the next space and what comes after is country code

If the first character is not numeric, anything to the left of the first space is the addressee name. What lies between that point and 2 spaces from the right is the zip code. Same approach as before, but now you probably have to worry about the zip code in the second example being in the wrong column when it is parsed. I imagine there would be other issues as well.

Doable, but would take some work. If you want to take a crack at it, look at these functions, which you'd have to use while looping over rows:
- Instr to find first space from left
- InstrRev to find 2nd space from right
- Left and Mid to parse the string bits
and code that parses these values to columns
HTH

 

[Alex Blakenburg]

You don't show what your expected outcome is. You can get to the below with just a few clicks using Power Query.
Would that be any good to you ?

Book1
	C	D	E
1	Address.1.1	Address.1.2	Address.2
2	1205 Madera Ave Madera	93637	US
3	3031 Franklin Blvd	95818-3938	US
4	28200 Wescott Ct	92596	US
5	FAITH BAPTIST TABERNACLE	32696-0000	US
6	3348 EDGEWOOD AVENUE	33916	US
7	1218 West St	96021-2943	US
8	109 S BROAD STREET	34601-0000	US
9	1210 W ROBINSON ST	32805	US
10	6910 STIRLING RD	33024	US
11	18335 Johnson Rd	96080-9467	US
12	2353 Fifth Ave	94518-1163	US
13	8510 WEST FLAGLER ST	33144	US
Sheet1

 

[Micron]

If one was doing text to columns 1205 would be in the first column. Not sure if that is what is wanted (as you say, no example given) but I presume PQ can do that. If so, what would happen to C5 if doing that made 4 columns?
I do have code for this but it looks like PQ (which I know nothing about) would be the way to go. It sure was complicated (for me, at least).

 

[Bud2014]

If one was doing text to columns 1205 would be in the first column. Not sure if that is what is wanted (as you say, no example given) but I presume PQ can do that. If so, what would happen to C5 if doing that made 4 columns?
I do have code for this but it looks like PQ (which I know nothing about) would be the way to go. It sure was complicated (for me, at least).

Thanks for the information. I don't know PQ. I've passed the problem back to the owner of the spreadsheet

 

[Micron]

Let me know if you want the code I wrote for this. I might still have it. Note - it separated 1205 from the street name. Not sure if that's what you wanted.