LinkBack URL
About LinkBacks
I have attached a small sample of a very large file that I need to complete this task for.
Ali, thank you for your your help, it is greatly appreciated.
I have attached a small sample of a very large file that I need to complete this task for.
Ali, thank you for your your help, it is greatly appreciated.
Last edited by ernie.burney; 04-05-2022 at 10:16 AM. Reason: Revised version of my initial file.
Please add some expected results.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Welcome to the forum.
The sample workbook also needs the results you are looking for, manually mocked up. Please add these and post the workbook again attached to your next post in this thread. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hello Ali, I just posted the revised version of the initial file.
This will split the addresses at any instance of " STE ", " FL ", or " UNIT "
=IFERROR(LEFT(A2,MAX(IFERROR(FIND({" STE "," FL "," UNIT "},A2),0))-1),A2)
If that is in E2 then this gives the bit that was removed (minus the space):
=IFERROR(RIGHT(A2,LEN(A2)-LEN(E2)-1),"")
The above solution doesn't exactly match your example file. Firstly the formatting is different - I don't know a way to convert the format to match your file using a formula. You could wrap it in PROPER() which will capitalise each word, but this also converts NE to Ne for example which isn't what you want. Secondly there are a couple of things that I assume are just mistakes, e.g. DISCOVERY DR --> Discovery Place. If that's not a mistake then we need to know the logic.
Hello Nick, thank you for your response. The formatting really isn't an issue, the file that I utilize to import the information capitalizes the address information. Yes, Discovery Dr. to Discovery Place was an error. Thank you for the formula, I tried using it, it worked on B2 but when I copied it to the other rows STE, FL and UNIT information did not delete. Please advise.
Nick, this is the file shows the error I mentioned. Thanks again.
Last edited by ernie.burney; 04-05-2022 at 11:18 AM. Reason: Formula
It worked fine when I tested it...please can you upload a file showing the error?
First formula needs to entered with Ctrl+Shift+Enter unless you have Excel 365.
Thanks John, always assume people have 365 unless they say otherwise!
I assume this is related, but when I open the file there is an @ in the formula that shouldn't be there.
John, Nick thank you for your responses. I do not have 365. I'm not sure how the At Sign is there...I copied and pasted the formula into the worksheet.
Can you please update your profile with your Excel version as responses do depend on knowing this information (as Nick has demonstrated!).
John, I have Excel 2016. I will update my profile accordingly. Thanks again.
No problem!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks