Using Windows 10 and Excel 2016.
Large data base that I have to clean up and rid the street name of the Aplt # after the street name, and in some cases, there is an Apt# right after the Street Number. See attached.
Using Windows 10 and Excel 2016.
Large data base that I have to clean up and rid the street name of the Aplt # after the street name, and in some cases, there is an Apt# right after the Street Number. See attached.
Not elegant, but this works (at least for your small sample)
=TRIM(LEFT(SUBSTITUTE(A2,"# ","#"),SEARCH("#",SUBSTITUTE(A2,"# ","#"))-1)&MID(MID(SUBSTITUTE(A2,"# ","#"),SEARCH("#",SUBSTITUTE(A2,"# ","#")),255),SEARCH(" ",MID(SUBSTITUTE(A2,"# ","#"),SEARCH("#",SUBSTITUTE(A2,"# ","#")),255)),255))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you.
This is a bit neater:
=TRIM(LEFT(A2,SEARCH("#",A2)-1)&" "&MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"# ","#"),LEFT(A2,SEARCH("#",A2)-1),""))&" "," ",REPT(" ",100)),100,100))
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
thank you. I will use it also
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks