Hello,
I need to extract the City and the State to their own columns to the right of Column BK, i.e. BL and BM with the State being capitalized (sometimes in the source file, the State is in lower case).
My contact list has the City and State combined under Column BK. They are not separated by a comma. See following format:
Lake View Terrace ca
Burbank ca
Sherman Oaks Ca
Thanks you for your help.
Is State always the two letter abbreviation...?
Try this for starters to check results are correct.
Please Login or Register to view this content.
Last edited by bakerman2; 06-10-2016 at 03:49 AM.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Hello, the State is always the 2 letter abbreviation. I ran the code offered by bakerman 2. I received an error message, Subscript out of range (Error 9) for the line, x1 = UCase(a(UBound(a))).
Here's what my code looks like with the range inserted.
SPlease Login or Register to view this content.
Last edited by SeaTiger; 06-10-2016 at 01:56 PM.
Please use code tags when posting code (select code and click #)
As for your question.
Please Login or Register to view this content.
Enter formula in B1 and pull it to C1 and then down
Formula:
Please Login or Register to view this content.
v A B C 1 Lake View Terrace ca Lake View Terrac ca 2 Burbank ca Burban ca 3 Sherman Oaks Ca Sherman Oak Ca
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hello bakerman2,
I'm still receiving the Subscript out of range (Error 9) for the line,Please Login or Register to view this content.
Hello Alkey, thanks for your formula. How can I correct the State so that both letters are capitalized?
Here is a modified formula that will capitalize states
Formula:Please Login or Register to view this content.
v A B C 1 Lake View Terrace ca Lake View Terrace CA 2 Burbank ca Burbank CA 3 Sherman Oaks Ca Sherman Oaks CA
I just assumed it was a continuous range without empty cells in between.
Please Login or Register to view this content.
Many thanks for you help. Here's one that I found that will create the columns BL and BM. You will need to change your work sheet name accordingly.
Please Login or Register to view this content.
Thanks for the rep.One observation though.
It's never a good idea to loop through cells on a worksheet. It slows down execution because of all that sheet interaction.
My code reads the entire range in memory, splits the values and writes results in 1 time back to your sheet. So 2 actions while you perform 200.
So my code takes less then 1/3 of the time you need.
Hello Bakerman2,
Thanks for the additional info. Can you add code to your formula so that is will create the 2 columns, BL & BM that the data extracts to? Thanks!
@ SeaTiger.
Your code in Post #11 does not capitalize as you yourself requested in Post #8 and it also stops at the first empty cell.
This could be what you want but it sounded like you might have empty rows (Post #10).
Hello Jolivanes, good catch....your right! I checked the list again after reading your post and the code in post # 11 it did not capitalze all the states. It turns our most of them were all ready capitalbized. Well, I welcome an updated code with that function.
should do it.Please Login or Register to view this content.
BTW, if there are empty cells in your range, I would loop, if that is what you want to do, with something like this.
The looping might be easier to understand but I would stick with bakerman2's code though.
Change the cell references as required.
Please Login or Register to view this content.
Last edited by jolivanes; 06-11-2016 at 04:52 PM. Reason: add info
Here is another loop you won't be using.
I would not give you all this if it wasn't raining cats and dogs out here and I could mow the lawn.
Please Login or Register to view this content.
Hello, I just run bakerman2's code again per post #10. It does everything that I requested. It created BL, BM columns witht he state capitalzied. Thanks again for everyone's comments and help.
Of course.
Hello bakerman2, my apologies, but after checking closer, what your code does is override the data that is all ready in BL & BM which I do not want to do. I should have disclosed that there was all ready data in thesee 2 columns. So, would you mind adding that code so that the City and State are extracted to BL, BK, with the data in the columns to the right of them get shifted over accordingly. Again if possible, the headings for the new columns would be tor_maling_city1 and tor_mailing_state1. Again, I am deeply grately for you help.
Please post example file of how your columns look like now and how you want it to be afterwards. Remember to desensitize information.
current file:
1st row is the Column reference, 2nd row is the Column Name, and balance of rows are the data. The worksheet columns range is A:DE without the 2 new colums added, A:DG with. In the desired file, thus Column BK would not change and BL now becomes BN because it shifted over.
BK BL tor_mailing_city tor_mailing_zip Culver City Ca 90230 Glendale Ca 91201 Glendale Ca 91206 La Mirada Ca 90638
desired file:
BL BM BN tor_mailing_city1 tor_mailing_state1 tor_mailing_zip Culver City CA 90230 Glendale CA 91201 Glendale CA 91206 La Mirada CA 90638
Last edited by SeaTiger; 06-11-2016 at 09:07 PM. Reason: I made a mistake in the column references and formating info.
Try this.
Please Login or Register to view this content.
I'm realizing that I need to be more clearer in my communication. Thank you for your understanding. I'm in the learning curve. Again, thank you for your efforts.
What the formula does in post # 23 is override the data in the pre-existing columns BM & BN so that the "trustee_name" and "tee_st" are replaced with the extracted data from BK "tor_mailing_city1" and "tor_mailing_state1" respectively. We do not want to lose this data. So, I re-did the desired file format, hoping that you understand what we need.
This is the current file format:
BK BL BM BN tor_mailing_city tor_mailing_zip trustee_name tee_st Los Angeles Ca 90230 Belgium Bank 1000 EXCEL RD
This is the desired file format whereas BK = no change, BL= is the city extracted from BK, BM = is the state abbreviation extracted from BK, BN = the zip code extracted from BL, BO= the trustee name extracted from BM and, BP = the turstee street extracted from BN. There is no change in the data in the balance of the columns (BP:DD) other than their columns are updated to be sequential in order:
BK BL BM BN BO BP tor_mailing_city tor_mailing_city1 tor_mailing_state1 tor_mailing_zip trustee_name tee_st Los Angeles ca Los Angeles CA 90230 Belgium Bank 1000 EXCEL RD
Last edited by SeaTiger; 06-12-2016 at 12:33 PM. Reason: corrected html code
So if I understand correctly by inserting 2 columns after BK and putting the split in those 2 new columns you're problems solved ?
Please Login or Register to view this content.
Last edited by bakerman2; 06-13-2016 at 12:44 AM.
Almost got it Buddy...however, now BN="tee_st" instead of "tor_mailing_zip"(it needs to be extracted from BL; see my post #24). BO and BP are fine as well as balance of columns
Post example file with the formulas you use to extract mailing_zip, trustee_name & tee_st.
Here is an example of the file format as reference without any code so that you can test your code.
Your profile doesn't say, but if you have Excel 2013 there is a feature called Flash Fill in the Data ribbon.
Type one example of each field as desired (including case) as below. Then with column C the active column apply Flash Fill. Next do column D and E in succession. A fly-out will prompt you for accept / undo. This worked at my end.
Row\Col Culver City Ca Culver City CA Glendale Ca Glendale CA Glendale Ca Glendale CA La Mirada Ca La Mirada CA
Dave
See revised code in post#25.
Code in Post # 25 works superb!!! Well done bakerman2. Tysm for your time and consideration. That goes for everyone else that provided help.
BTW, I have Excel 2010 so don't believe I've seen Flash Fill so post # 29 solution is not available to me.
You're very welcome.
Sorry for the delay but was a bit confused about layout. Once visualized it was a piece of cake.
@bakerman2
Re: "Piece of cake"
Vlaamse vlaai?
Originally Posted by SeaTiger
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks