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!

 

[Peter_SSs]

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!!

Ken

This places 99 spaces in the text everywhere there was one space to start with.

Then, RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99) takes the right hand 99 characters of that result. Unless your last 'word' in the text is longer than 99 characters, this will return the last 'word', preceded by a lot of spaces.

TRIM then cleans off the spaces leaving the last 'word' of the original text.

 

[ken2step]

Peter,
Thanks for that explanation! Very creative indeed and I understand how it works now.

 

[Peter_SSs]

Very creative indeed

I certainly am not taking credit for that. I picked it up from this forum, just as you have now.