+ Reply to Thread
Results 1 to 23 of 23

Excel Formatting & Capabilities Question

  1. #1
    Registered User
    Join Date
    01-13-2016
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2013
    Posts
    6

    Question Excel Formatting & Capabilities Question

    (NEWBIE)

    I have a mailing list I put together of the last several months; over 2000+ Name and Addresses. How do I configure my list in a fast/quick/easy way in to the right format using Excel or some other type of computer software? I need for this same list to be corrected/modified by adding the necessary commas within the right areas for importing into a Mailing Software. Would I have to manually add the commas (comma delimited) in the right areas one-by-one using Excel or is there a software out their - that will do that for me fast and automatically (so-to-speak)?

    EXAMPLE OF THE CORRECT FORMAT I AM LOOKING FOR: ABC Company, 1234 Jones Blvd, Las Vegas, Nevada, 89119, [email protected]

    REQUIREMENT: I need six fields/grids: Company, Street/Mail Address, City, State, Zip-code and Email Address – ALL separated properly with a comma in the right areas.

    Here is a sample of my list (Names & Addresses Have Been Modified For Privacy Issues):

    ABC Fraternity - 1160 Peachtree Street, NW, Suite: 1604, Atlanta, Georgia 30303
    Good Day - 2003 Saint Paul Street, Baltimore, MD 21218 - [email protected]
    Westgate Services, 2311-24 North Broad Street, Philadelphia, PA 19132-4590 - [email protected]
    Omega Fraternity, Inc. - 3221 Snapfinger Parkway, Decatur, Georgia 30035 - [email protected]
    Walstore Inc. - 105 Kennedy Street, NW Washington, DC 20011-5294 - [email protected]
    Sigma, Inc - 7022 Commerce Street, Suite 620, Dallas, Texas 75202
    Cookie - Founders Hall, 1600 N. Calvert Street, Baltimore, MD 21202 – [email protected], [email protected]
    Last edited by acvegas; 01-13-2016 at 04:36 PM. Reason: Recommended by Moderator

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Comma Here, Comma Their (Configuration Issue)

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Ben Van Johnson

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Formatting & Capabilities Question

    Agree that the title needs work, but would also add that I see no way to do this with either a formula or with code The data (sample) is really messy, in some cases you want a comma after 1 word, other times after 2 word,and others many more words. How would you expect excel which words are Company and which are address? Even looking at something like that manually could be a hit-and-miss affair if you are not familiar with names.

    Keep in mind that to excel, the names mean nothing, they are just a string of letters and numbers.

    Do you at least have some sort iof list that would contain all the bits of info?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-13-2016
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2013
    Posts
    6

    Re: Comma Here, Comma Their (Configuration Issue)

    Quote Originally Posted by protonLeah View Post
    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Sorry for the confusion. I did not see the "Rules Section" within your site; yes I know it's here. If the new title is not appropriate I would appreciate you and helping me choosing one. Thanks.

  5. #5
    Registered User
    Join Date
    01-13-2016
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2013
    Posts
    6

    Re: Excel Formatting & Capabilities Question

    Quote Originally Posted by FDibbins View Post
    Agree that the title needs work, but would also add that I see no way to do this with either a formula or with code The data (sample) is really messy, in some cases you want a comma after 1 word, other times after 2 word,and others many more words. How would you expect excel which words are Company and which are address? Even looking at something like that manually could be a hit-and-miss affair if you are not familiar with names.

    Keep in mind that to excel, the names mean nothing, they are just a string of letters and numbers.

    Do you at least have some sort iof list that would contain all the bits of info?
    Nope; when I created the list (I am new to this) I did not realize a proper format was needed for the software I will be uploading this same file to. I agree with you the file I have created is messy. Now in the future I clearly understand what correct format I must use. That's why I am posting my question here just hoping for some type of software miracle to correct my mistakes and automatically place the commas in the right place. I will probably have to hire a typist/editor secretary to manual do it for me one-by-one; since my own time is very limited now.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Formatting & Capabilities Question

    Like I said, I see no way to do this, but I have seen some of our experts do amazing things, I will push this out and see what comes back

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Excel Formatting & Capabilities Question

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Excel Formatting & Capabilities Question

    Are those dashes line seperators for the forum or on your worksheet? You haven't supplied a sample workbook yet.

  9. #9
    Registered User
    Join Date
    01-13-2016
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2013
    Posts
    6

    Re: Excel Formatting & Capabilities Question

    Quote Originally Posted by davesexcel View Post
    Are those dashes line seperators for the forum or on your worksheet? You haven't supplied a sample workbook yet.
    Okay; below is the correct format shown that I am looking for as presented/outlined by the software manufacture (Easy Plus Mail) for importing my mailing list into their software:

    NOTE: Names & Address Show Below Are Not Real Persons.

    "First Name","Last Name","Organization","Address# 1","City","State","Postal Code","Phone# 1","Email"
    "","","","","","","","",""
    "","","","","","","","",""
    "","","","","","","","",""
    "","","","","","","","",""
    "Ethel","Ackerman","Elliott Inc.","1225 Vienna Dr. #438","Sunnyvale","Ca","94089","(408)734-8623","[email protected]"
    "Elizabeth","Baca","Lougheed Inc.","482 Alexander Way","Milpitas","Ca","95035","(510)569-8220","[email protected]"
    "Dow","Caffee","Solovey Inc.","17667 Pond-De Rosa Ln.","Folsom","Ca","93908","(408)624-0610","
    "Wilson","Dale","Harowitz Inc.","1089 Rockefeller Drive","Sunnyvale","Ca","94087","(408)738-4359","[email protected]"

    So what I would have to do is “manually” format my current list (2000+) into this format. It’s time consuming; but I figure out that I could use a simple text file to do the proper editing. By simply using Find/Delete & Replace, etc editing on my current mailing list. (END)
    Last edited by acvegas; 01-14-2016 at 08:33 AM. Reason: Typo

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Excel Formatting & Capabilities Question

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    To add a file to a new post

    To add a file to an existing post.

  11. #11
    Registered User
    Join Date
    04-29-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Formatting & Capabilities Question

    I would agree with FDibbins - the variation within the company names and addresses, even in that small sample would make this very difficult to get right.

    You could perhaps speed up the process, you could use a macro with the regular expression search to extract the zipcode, for example:
    Please Login or Register  to view this content.
    -assuming the data is a single string in column A, this will put the zip codes in column B you will need a reference to the "Microsoft vbscript regular expressions 5.5".

    Validating e-mail addresses can be quite complex in regex - if it is always at the end and has space, you could use the split function in vba to parse by spaces and then take the upper bound of the resulting array.

    that would leave you with the company and address, which I think you probably need to do manually.

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Excel Formatting & Capabilities Question

    Hi acvegas

    I struggle as well with some of my own earlier made messy lists, having unfortunately not taken enough care initially. I sympathise, - it is difficult when you make lists before you learn any programming, as i did.
    There is a thing in VBA called the Collections Object ( EDit - Think I mean Regex Stuff *****). It seems to be able to do close to wonders, guessing the correct format something like Google often does. I have seen some experts here do wonders with it to sort messy lists into a more ordered one, for example..
    http://www.excelforum.com/excel-prog...ml#post4200033
    This requires accordingly an extreme level of expertise. I suspect the people in the world that could apply the Collections Object solution successfully to your data you can count on one hand, but you may be lucky as i think they may participate in this forum sometimes..
    Unfortunately i am just a beginner / Novice myself and cannot mannage that Collections Object stuff!. But

    How about an in between solution that might help a bit. It probably does not do the job for you completely, and with more real data it would probably be even worse.

    _ But the idea is this:

    _1) You copy your data to the first Excel Spreadsheet column. I use for now the data from Post #1. , and I go with your requirement from then for now, ( You seem to have just changed what you want in your last post ? )

    _2 ) you run the code i give you. It attempts to chop up the data as you want it into columns C to G

    _3) You would probably then need still to scroll down manually and make some corrections where the code got it wrong.

    _4) Having done that it is fairly easy to write a code that will convert those new columns into a text file in the form that you want.

    _..................

    _ So I start with the data you gave pasted in column A ( I leave as is usual practice the first Row empty for use as a Header)

    Using Excel 2007
    Row\Col
    A
    2
    BC Fraternity - 1160 Peachtree Street, NW, Suite: 1604, Atlanta, Georgia 30303
    3
    Good Day - 2003 Saint Paul Street, Baltimore, MD 21218 - [email protected]
    4
    Westgate Services, 2311-24 North Broad Street, Philadelphia, PA 19132-4590 - [email protected]
    5
    Omega Fraternity, Inc. - 3221 Snapfinger Parkway, Decatur, Georgia 30035 - [email protected]
    6
    Walstore Inc. - 105 Kennedy Street, NW Washington, DC 20011-5294 - [email protected]
    7
    Sigma, Inc - 7022 Commerce Street, Suite 620, Dallas, Texas 75202
    8
    Cookie - Founders Hall, 1600 N. Calvert Street, Baltimore, MD 21202 – [email protected], [email protected]
    acvegas

    _.....

    After running my code You get this:

    Row\Col
    C
    D
    E
    F
    G
    H
    1
    Company Street/Mail Address City State Zip-code Email Address
    2
    BC Fraternity 1160 Peachtree Street NW Atlanta Georgia 30303
    3
    Good Day 2003 Saint Paul Street Baltimore MD 21218 [email protected]
    4
    Westgate Services 2311-24 North Broad Street Philadelphia PA 19132-4590 [email protected]
    5
    Omega Fraternity Inc. 3221 Snapfinger Parkway Decatur Georgia 30035 [email protected]
    6
    Walstore Inc. 105 Kennedy Street NW Washington DC 20011-5294 [email protected]
    7
    Sigma Inc 7022 Commerce Street Dallas Texas 75202
    8
    Cookie - Founders Hall 1600 N. Calvert Street Baltimore MD 21202 [email protected], [email protected]
    acvegas


    (_ You will note it only gets the first of your Rows wrong. You had a very wiered order of things there)
    _ The program is unfortunately very messy, mirroring your messy data. I made up the Replacing bits as i went along using your test data.
    _ But I have 'commented my code extensively, so You may be able, once You have understood it, to play around a bit more with it to get exactly what You want

    Hope that is some help
    Please let us know how you get on
    Alan



    Code is here:

    http://www.excelforum.com/developmen...ml#post4288852

    and here

    http://www.excelforum.com/developmen...ml#post4288953


    Note it is all one code. I had to split it to get it all in due to Forum Post Size Limitations

    You Need to Copy the second part directly under the first part in the Same Code Module




    ( EDit: I think I meant Regex Stuff there ****** )
    Last edited by Doc.AElstein; 01-15-2016 at 07:13 AM. Reason: Mixed up collectioss stuff with Regex stuff ( Poo :( )
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  13. #13
    Registered User
    Join Date
    01-13-2016
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2013
    Posts
    6

    Thumbs up Re: Excel Formatting & Capabilities Question

    Okay; now how much will you charge to run mine; around about 1000 names or so? Note: what you are explaining to me is too advanced (way above my head). Again - how much would you charge?
    Quote Originally Posted by Doc.AElstein View Post
    Hi acvegas

    I struggle as well with some of my own earlier made messy lists, having unfortunately not taken enough care initially. I sympathise, - it is difficult when you make lists before you learn any programming, as i did.
    There is a thing in VBA called the Collections Object. It seems to be able to do close to wonders, guessing the correct format something like Google often does. I have seen some experts here do wonders with it to sort messy lists into a more ordered one, for example..
    http://www.excelforum.com/excel-prog...ml#post4200033
    This requires accordingly an extreme level of expertise. I suspect the people in the world that could apply the Collections Object solution successfully to your data you can count on one hand, but you may be lucky as i think they may participate in this forum sometimes..
    Unfortunately i am just a beginner / Novice myself and cannot mannage that Collections Object stuff!. But

    How about an in between solution that might help a bit. It probably does not do the job for you completely, and with more real data it would probably be even worse.

    _ But the idea is this:

    _1) You copy your data to the first Excel Spreadsheet column. I use for now the data from Post #1. , and I go with your requirement from then for now, ( You seem to have just changed what you want in your last post ? )

    _2 ) you run the code i give you. It attempts to chop up the data as you want it into columns C to G

    _3) You would probably then need still to scroll down manually and make some corrections where the code got it wrong.

    _4) Having done that it is fairly easy to write a code that will convert those new columns into a text file in the form that you want.

    _..................

    _ So I start with the data you gave pasted in column A ( I leave as is usual practice the first Row empty for use as a Header)

    Using Excel 2007
    Row\Col
    A
    2
    BC Fraternity - 1160 Peachtree Street, NW, Suite: 1604, Atlanta, Georgia 30303
    3
    Good Day - 2003 Saint Paul Street, Baltimore, MD 21218 - [email protected]
    4
    Westgate Services, 2311-24 North Broad Street, Philadelphia, PA 19132-4590 - [email protected]
    5
    Omega Fraternity, Inc. - 3221 Snapfinger Parkway, Decatur, Georgia 30035 - [email protected]
    6
    Walstore Inc. - 105 Kennedy Street, NW Washington, DC 20011-5294 - [email protected]
    7
    Sigma, Inc - 7022 Commerce Street, Suite 620, Dallas, Texas 75202
    8
    Cookie - Founders Hall, 1600 N. Calvert Street, Baltimore, MD 21202 – [email protected], [email protected]
    acvegas

    _.....

    After running my code You get this:

    Row\Col
    C
    D
    E
    F
    G
    H
    1
    Company Street/Mail Address City State Zip-code Email Address
    2
    BC Fraternity 1160 Peachtree Street NW Atlanta Georgia 30303
    3
    Good Day 2003 Saint Paul Street Baltimore MD 21218 [email protected]
    4
    Westgate Services 2311-24 North Broad Street Philadelphia PA 19132-4590 [email protected]
    5
    Omega Fraternity Inc. 3221 Snapfinger Parkway Decatur Georgia 30035 [email protected]
    6
    Walstore Inc. 105 Kennedy Street NW Washington DC 20011-5294 [email protected]
    7
    Sigma Inc 7022 Commerce Street Dallas Texas 75202
    8
    Cookie - Founders Hall 1600 N. Calvert Street Baltimore MD 21202 [email protected], [email protected]
    acvegas


    (_ You will note it only gets the first of your Rows wrong. You had a very wiered order of things there)
    _ The program is unfortunately very messy, mirroring your messy data. I made up the Replacing bits as i went along using your test data.
    _ But I have 'commented my code extensively, so You may be able, once You have understood it, to play around a bit more with it to get exactly what You want

    Hope that is some help
    Please let us know how you get on
    Alan



    Code is here:

    http://www.excelforum.com/developmen...ml#post4288852

    and here

    http://www.excelforum.com/developmen...ml#post4288953


    Note it is all one code. I had to split it to get it all in due to Forum Post Size Limitations

    You Need to Copy the second part directly under the first part in the Same Code Module

  14. #14
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Excel Formatting & Capabilities Question

    Hi acvegas
    _ Just one quick thing.. when you reply with a quote, edit out all but anything relevant, or just edit out most of it. That makes the Thread a bit less cluttered and easier to read.. so reply like i do here:.......
    ........
    _ .....................
    Quote Originally Posted by acvegas View Post
    Okay.....
    what you are explaining to me is too advanced ..... ... how much would you charge?
    Ha Ha! . LOL !
    _. I only come here occasionally when I have the time, to help and learn myself ( or remember) the little of VBA I know! ( I think there is a commercial section here somewhere, where you can pay for stuff, but I have no idea about that – that is something the “Big Boys” sometimes get involved with. – I am about 30,000 posts away from that, and have no interest in that anyway )
    _. Really I have "done" the "work" already. If you have the slightest idea macros / VBA then you just do a couple of clicks.. and based on your initial requirement it should do all or come very close to what you want. So I have already done it all "voluntary". A “Paid” solution would ( I think ) just do what i have done. ( Bit without the many explaining bits – that is “extra” you do not normally get, and would be a further “paid” job!, -( I guess ). And of course, by paying, you might get a more professional code type. Mine uses very basic techniques. But you would understand that even less. Bit As the more experienced people have said here, your list is so messy it may be impossible to get a bullet proof solution, and it would not work any better than mine, - ( maybe a bit quicker - but for 2000 rows even a "slower" VBA code is usually more thann fast enought, rarely more than a couple of seconds for this sort of code. And mine i could easilly speed up, by taking out imbetween bits that help to explain and show what is going on,..... ....
    ... but I am constantly amazed what some experienced people are capable of..)


    _ . I assumed you had very basic VBA knowledge and could run a macro. If not and you are interested and are not in a rush I can try and walk you through getting started. I may have a bit of time tomorrow. After that i am off again until next week. The code i wrote is well explained in the 'Comments.. So once you get started you can easily fiddle with it and see how it effects your results. But at the end of the day, as ( if ) your data gets more complicated and extra bits need to be written to allow for that, then there may come a point where it would be quicker just to get a crate of Beer, sit down , and do it all manually anyway..
    ( _. But note: - if your test data was very representative of the all types you have then the code should work with no modifications for over 60,000 rows. ( As i have written the code it is not set to a row limit – one of the first things the code does is determines how many rows you have pasted in column A . – check out this bit near the start:
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row with something in in Column 1........
    _ . That bit determines how many rows you have, and after that the code does everything to all those rows.
    _ ...
    )

    _. In the meantime let me know how much you do know, and what version of excel you use.

    Alan


    P.s. This is the usual gem given to newbies.......

    Open a fresh file of yours, and put in your test data ( As I suggest in column A ) ( Note again my code is based on your test data given in Post #1 )
    Change the Worksheet name in which you put that data to that name I use in the code ( acvegas )
    To install the macro, simply press ALT+F11 to go into the VB Editor and, once there, normally you will see a big empty window. ( If not, click Insert/Module on the VB Editor menu bar, and a new Window should come up )
    Then copy/paste the given code into the code window. That's it.... you are done.
    To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name ( acvegas ) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


    (IMPORTANT: always save your File just before you run any program. -Because you cannot use the Excel backward thing to clear any changes made by a macro. If anything does then go wrong, just close the File without saving it and re-open it, and try again!!)
    Last edited by Doc.AElstein; 01-15-2016 at 07:27 AM.

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Formatting & Capabilities Question

    Quote Originally Posted by acvegas View Post
    Okay; now how much will you charge to run mine; around about 1000 names or so? Note: what you are explaining to me is too advanced (way above my head). Again - how much would you charge?
    This is a free forum. While we do have a Commercial Services forum where you can pay senior members to devote time and effort to your question, all other forums here are replied to here by volunteers, who offer their help for free

  16. #16
    Registered User
    Join Date
    01-13-2016
    Location
    Las Vegas, Nevada
    MS-Off Ver
    2013
    Posts
    6

    Re: Excel Formatting & Capabilities Question

    I assumed you had very basic VBA knowledge and could run a macro. If not and you are interested and are not in a rush I can try and walk you through getting started. I may have a bit of time tomorrow. After that i am off again until next week. The code i wrote is well explained in the Comments. So once you get started you can easily fiddle with it and see how it effects your results. But at the end of the day, as (if) your data gets more complicated and extra bits need to be written to allow for that, then there may come a point where it would be quicker just to get a crate of Beer, sit down, and do it all manually anyway.
    Nope – I have never ran VBA or Macro before. I am a quick learner …..but? I am using Excel 2013; tomorrow I start my work schedule again for the next four days (10 Hour Shift Per Day). I would have to get back with you on that.

  17. #17
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Excel Formatting & Capabilities Question

    Quote Originally Posted by acvegas View Post
    Nope – I have never ran VBA or Macro before. I am a quick learner …....
    ..OK , reply back here later f you wish..
    .....I should get a Notification if you post again.....
    Alan

  18. #18
    Registered User
    Join Date
    04-29-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Formatting & Capabilities Question

    Please Login or Register  to view this content.
    Is the best I've come up with, some manual changing of the addresses/companies will be needed but it should work for 50%+ of your data.

    Press alt+F11 to open the VBA developer, click insert->module and then copy that code into the module and press F5 to run.

    this assumes that you have all of your data in column A on sheet1 and a list of state codes/names in sheet2 (see attached workbook)
    Attached Files Attached Files

  19. #19
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Excel Formatting & Capabilities Question


    @ jmhuk
    Hi,

    If you have time, can I trouble you for a couple of follow up questions, regarding your code from Post #11.

    _1) Do you happen to know if there is a late binding version for RegExp that would work with your codes. ( I personally use Early binding most to get intellisense, but for sharing try to give the late binding option also ). Or is this one of those weird cases where only Early binding works!

    _2) Regarding your Zip Code Code ( Post #11 ). I have read up now on some Regex Stuff. I think I understand that this “Pattern”
    __________________"([0-9]{5}[^0-9][0-9]{4}|[0-9]{5})"
    Is the same as
    ___"(([0-9][0-9][0-9][0-9][0-9])([^0-9])([0-9][0-9][0-9][0-9])|([0-9][0-9][0-9][0-9][0-9]))"

    And i can see that it is suggesting it matches things with a “Pattern” like these four examples:
    __34589-3456____23991b4567_____47781 5678___47781 1234

    I got bogged down on how it was also picking out things like:
    ____34998________57889

    By lots of experimenting I think I got it...
    This will not work
    ___"([0-9]{5}[^0-9][0-9]{5}|[0-9]{4})"
    As it just returns me the first occurrence of 4 number characters.
    _____ Your original condition could be written
    ____"(([0-9]{5}[^0-9][0-9]{4})|[0-9]{5})"

    So I have here 2 main conditions ( or “Patterns” ). And it ( VBA ) works from the left, first it tries to satisfy this
    _____[0-9]{5}[^0-9][0-9]{4}____which is ( 5 digits & anything other than a digit & 4 digits )
    Only if it does not find that it goes on to look for 5 digits
    ____"[0-9]{5}"

    I think in trying to prepare my question I have answered my question. So a simple yes to my statement “I have here 2 main conditions ( or “Patterns”). And it works from the left, first it tries to satisfy the first, and only goes on to try the second, if the first is not satisfied. “ would suffice here i think.
    _ . My statement is a fundamental point that was not clear to me from any googling

    Here is a Demo Code for the above
    Please Login or Register  to view this content.
    Thanks,
    Alan

  20. #20
    Registered User
    Join Date
    04-29-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Formatting & Capabilities Question

    @Doc:

    1. CreateObject("vbscript.regexp")
    2. In the phrase: "([0-9]{5}[^0-9][0-9]{4}|[0-9]{5})", the | operator means OR. So it asks for either [0-9]{5}[^0-9][0-9]{4} or [0-9]{5}, the first one asks for 5 numbers, followed by one character which is not a number (^ is the not operator), followed by 4 numbers. Square brackets denote the type of character and curly brackets denote the number it should be repeated. The second one asks for 5 numbers only.

    With regards to the statement:
    “I have here 2 main conditions ( or “Patterns”). And it works from the left, first it tries to satisfy the first, and only goes on to try the second, if the first is not satisfied. “

    (I think) In the case where more than one match is found, it will return all of them as an array - the use of the (0) in:
    Range("I" & a).Value = regEx.Execute(strInput)(0)
    Is what determines which one to return to the cell.

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Excel Formatting & Capabilities Question

    @ jmhuk
    Hi, thanks for that quick reply

    _1 ) Got it..
    Please Login or Register  to view this content.
    Thanks.
    _....................

    _2) My ....“I have here 2 main conditions ( or “Patterns”). And it works from the left, first it tries to satisfy the first, and only goes on to try the second, if the first is not satisfied. “… was not too clear what i meant. The other stuff you said was helpful, and confirmed what I thought i had understood from googling: I’ve got the point of the __|__ works as an __Or__ But ( I think still) it only does the second if it does not find the first, like I thought VBA does generally.

    For example, This
    ___"([0-9]{5}|[0-9]{5}[^0-9][0-9]{4})"_____( your code line swapped around the | )
    Only returns the first 5 digits in your code for all rows.

    But maybe your Array idea is correct and/ but also my idea.....,.....

    To explain:

    _ .... if I put Zip Codes like___ 12564-5992___ in all rows.. only (0) works. (1) always seems to give an error.

    But: - if I put say in row 2 __30303-1239 __ 40303-1239 , that is to say 2 Zip Codes, then step through with your code modified a bit..

    ____.Pattern = "([0-9]{5}|[0-9]{5}[^0-9][0-9]{4})"
    And
    ____ Range("I" & a).Value = regEx.Execute(strInput)(0)
    ____ Range("I" & a).Value = regEx.Execute(strInput)(1)
    _........... First I get out
    30303
    Then
    40303
    _...
    Similarly with
    ____ .Pattern = "([0-9]{5}[^0-9][0-9]{4}|[0-9]{5})"_____( your original code line )
    and
    ____ Range("I" & a).Value = regEx.Execute(strInput)(0)
    ____ Range("I" & a).Value = regEx.Execute(strInput)(1)
    Stepping through returns
    30303-1239
    Then
    40303-1239
    _...
    ( An extra line
    ___Range("I" & a).Value = regEx.Execute(strInput)(2)
    Still does not work )
    _......

    So it is all tying up
    :
    _a) The second Pattern will not be tried if the First Pattern “Works” one or more times.
    _b) You can get at the different found match with the (n) bit where n goes from 0 , 1, 2 etc where the total number of digits is equal to the number of matches there
    _c) A last bit i noticed. I cannot seem to get out of
    regEx.Execute(strInput)()
    or
    regEx.Execute(strInput)
    an Array with something like
    Dim vTemp As Variant
    vTemp = regEx.Execute(strInput)()
    or
    Dim vTemp As Variant
    vTemp = regEx.Execute(strInput)

    So this suggest that
    _____.Execute(strInput)
    either
    _c)(i) can “hold” multiple found matches somehow somewhere, and we can access them as described above, but not get an Array ( directly ) from it.
    Or
    _c)(ii)
    the (n) bit somehow tells it which one to “take” and return.
    As .Execute is a Method and I geuss the syntax is .Excecute(LongArgument) , where the argument is not optional and its valid range is based on what we have discussed.
    My money is on the latter, _c)(ii) !!

    Alan

    P.s.: - bit more experimenting Done........
    This always works...
    Dim vTemp As Variant
    Set vTemp = regEx.Execute(strInput)
    Or
    Dim vTemp As Object
    Set vTemp = regEx.Execute(strInput)

    So an object is returned, and like many objects
    ____ __________regEx.Execute(strInput)(0)
    ____ _________ regEx.Execute(strInput)(1)
    Is a just a “shorthand”
    For
    ____ __________regEx.Execute(strInput).item(0)
    ____ _________ regEx.Execute(strInput).item(1)


    P. P.s Trying yo
    ur code just now.. getting this


    Row\Col
    B
    C
    D
    E
    F
    G
    2
    ABC Fraternity 1160 Peachtree Street NW Suite: 1604 Atlanta GA 30303
    30303
    3
    Good Day 2003 Saint Paul Street Baltimore MD 21218 [email protected]
    4
    Westgate Services, 2311 24 North Broad Street Philadelphia PA 19132-4590 [email protected]
    5
    Omega Fraternity, Inc. 3221 Snapfinger Parkway Decatur GA 30035 [email protected]
    6
    Walstore Inc. 105 Kennedy Street NW Washington DC 20011-5294 [email protected]
    7
    Sigma, Inc 7022 Commerce Street Suite 620 Dallas TX 75202
    75202
    8
    Cookie Founders Hall 1600 N. Calvert Street – grandpo234ATiotaphitheta.org Baltimore MD 21202 [email protected]
    acvegas

    _.. so the OP has another interesting alternative solution to consider.
    ( P.s. I think you have not Declared you i variable, but the code still works without an Option Explicit )
    Last edited by Doc.AElstein; 01-15-2016 at 05:15 PM.

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Excel Formatting & Capabilities Question

    acvegas ,

    Here's my interpretation of your problem.
    If this is not even close to what you are after, just ignore.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 01-15-2016 at 08:19 PM.

  23. #23
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Excel Formatting & Capabilities Question

    Hi jindon,
    Quote Originally Posted by jindon View Post
    Here's my interpretation of your problem.
    ........
    _ OK. The OP’s start point is a very messy list of rows ( stored somewhere. He was not too specific where, but it is probably a text file or similar. May even be an excel file. But that is not important. ) - He gave a typical set of 7 messy rows in Post # 1.
    He wants those to be sorted into a neat list of tidy rows. The final list could be a Comer delimited file. But that is also not important.

    _ The main point of the excessive to get those messy rows sorted into neat ordered tidy rows. Me and jmhuk suggested putting the initial messy rows all in column A , then attempting to place the ordered sections for each row in other columns to the right. Once that was done it would be straight forward to join all those columns back into ordered rows, with the sections separated by Comers or wotever ( whatever ).. The reason why me and jmhuk suggested pasting out to columns initially was so that the OP could then check through and make any necessary corrections manually before the columns would then be joined back to produce ordered rows. We suggested this as we thought it was barely possible to take such a messy list of rows such as the OP had and fully by VBA code get it perfect. I expect if such a thing is humanly possible, it may take someone of your abilities to achieve that. But we expect the nature of the OP’s messiness is such that it is just not possible

    _ Along the way, the OP also produced some simple ordered rows, ( not quite representing the actual data headings he wants, which he also gave in Post # 1 ) . This was done, i think, just to demonstrate a nice “clean” ordered list of rows, as a comparison with his messy list of rows.

    _ I do not quite follow all your code workings, but you appear to be importing this set of ordered rows into am excel File.
    _ Apologise if i am wrong, But I think what you have done has no relevance to the OP’s requirement. But I may have missed something

    Alan

    P.s. I am particularly intrigued as to what this part in your code does:
    .Pattern = "\b\S{1,3} "
    I have only just started looking at Regex stuff and i cannot figure out that line.
    _ could you explain that bit please.
    I think i almost follow most of the other workings in your code.
    Thanks
    Last edited by Doc.AElstein; 01-21-2016 at 08:11 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Put a comma before three digit if comma is missing
    By sroysroy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-01-2016, 01:01 PM
  2. Put a comma before three digit if comma is missing
    By sroysroy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2015, 12:06 PM
  3. Comma delimited numbers with space after comma
    By Cicada in forum Excel General
    Replies: 5
    Last Post: 04-23-2015, 09:32 AM
  4. [SOLVED] Code to Replace Comma with dot and dot with comma
    By BS Singh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2014, 06:10 PM
  5. Find and remove all text to the left of last comma (including comma) in a cell
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-05-2013, 08:47 AM
  6. [SOLVED] If comma present, then place a character before and after comma (FORMULA)
    By jaimealvarez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2012, 04:04 PM
  7. replace the comma separator to a dot and dot to a comma at the same time
    By barkiny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2010, 05:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1