I need your assistance please on a formula which will extract all the 'mid' characters of a string. An example of the data is below:
0000359Russell Ln
0002313W Arkansas Ln # 111
0000615Stadium Dr
0002025S Cooper St
0001001Waldrop Dr # 702
0003132Matlock Rd # 307
0000915W Mitchell St
0000925E Mitchell St
0001863W Division St # B
I am looking for the data to be returned as:
Russell Ln
Arkansas Ln
Stadium Dr
Cooper St
Waldrop Dr
Matlock Rd
Mitchell St
Mitchell St
Division St
Thank you very much!
Can we trust the # to precede the part at the end that is removed?
=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",255)), 8,255))
Last edited by mikerickson; 09-14-2012 at 01:58 AM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
To answer your question, one of the four values will precede the part at the end that is removed:
#
APT
FLR
STE
is it always 8 charicters at the start you need removed?
The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
<--- If a post helps hit the star
Pl see attached file with macro.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks