Another Reverse Concatenation Issue

[Josh42]

Hello all.

I need to reverse concatenate a column of addresses, but text to columns won't work. I'd like to have a formula that takes into account each of the following scenarios (basically any standard address you can think of):

102 Bart St
104 Homer Simpson Ave
106 US HWY BSN 805 W
108 N Springfield Rd

What I need is to have the result in four columns. The first field would the house number. The second column would be the prefix (direction) of the street name, IF PRESENT (so the first three examples would have no value in the second column, but the fourth one would have an "N". The FOURTH column would have the suffix, whether that is a street type (like Rd or Ave), or a post-directional like in the third example ("W"). The THIRD column would have everything else (whatever is between the prefix and the suffix). In other words, using the examples above (* indicating a new column):

102**Bart*St
104**Homer Simpson*Ave
106**US HWY BSN 805*W
108*N*Springfield*Rd

Thanks for any help!

 

[Ed in Aus]

Welcome to the board,

I have a little bit of an idea but I would need a lot more address to test if it works, can you please post a few more so i can give it a go?

By the way i don't like the chances of my "Idea" working that well.

 

[Peter_SSs]

Josh42

Welcome to the MrExcel board!

As Ed has indicated, there can be so many variations in this sort of data it can often be hard to come up with a fool-proof solution. However, I've had a stab based on the data provided. See if these formulas (copied down) do what you want. If not, we certainly need some more examples of the data and expected results.

Excel Workbook
	A	B	C	D	E
1	102 Bart St	102		Bart	St
2	104 Homer Simpson Ave	104		Homer Simpson	Ave
3	106 US HWY BSN 211 W	106		US HWY BSN 211	W
4	108 N Springfield Rd	108	N	Springfield	Rd
Split Text

 

[Josh42]

Thanks - wasn't expecting a response that quickly! Here are some more.

Oh, and just to throw a wrench into this, I just thought of a potential problem. Some of the addresses should not have a suffix - namely those that are US HWYs without a post-directional. I.E., 282 US HWY 29 should not have a suffix. Basically, any address that ends in a numeric value should have no value in the fourth column, so everything after the 2nd column (pre-directional) should be in the third column. If you can't figure a way to work this rule in, no big deal. Thanks again!

5 Albert Ln
282 US HWY 29
492 US HWY 320 W
26 Ingrid Heights St
9343 N Falls Chapel Ln
43 W Maryland Ave
2 E River Water Valley Ln
932 Western Ave
22 US HWY BSN 340 S

Results:

5**Albert*Ln
282**US HWY 29 (no 4th column value)
492**US HWY 320*W
26**Ingrid Heights*St
9343*N*Falls Chapel*Ln
43*W*Maryland*Ave
2*E*River Water Valley*Ln
932**Western*Ave
22**US HWY BSN 34*S

Is this enough?

 

[Ed in Aus]

Wow, just tested all of these with Peter's post (all work as usual), Peter your good...

 

[Biz]

Problem with 282 US HWY 29

Try cell E1

=IF(ISNUMBER(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))+0),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

 

[Peter_SSs]

Problem with 282 US HWY 29

Try cell E1

=IF(ISNUMBER(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))+0),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

Correct, but I think this shorter one should do the same job in the fourth column (Col E):

=IF(ISNUMBER(RIGHT(A1)+0),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

 

[Biz]

Peter very nice.

 

[Josh42]

Wow, to think I messed around with this most of the day! The last update works perfectly, and I'm checking them on a much larger list and it's working there, too.

Joining this board was basically an act of desperation. You guys are amazing. Peter - you're a miracle worker - THANKS!

 

[ken2step]

Question for Peter...

Can you explain how the SUBSTITUTE(A1," ",REPT(" ",99)),99) part of the formula in E1 works?

Very slick how that works by the way!!