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, abc@yet.net

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 - support@apa1123.net
Westgate Services, 2311-24 North Broad Street, Philadelphia, PA 19132-4590 - info@kappaaweqsi1911.com
Omega Fraternity, Inc. - 3221 Snapfinger Parkway, Decatur, Georgia 30035 - info@osdpf.org
Walstore Inc. - 105 Kennedy Street, NW Washington, DC 20011-5294 - info@ibfghsigma1914.org
Sigma, Inc - 7022 Commerce Street, Suite 620, Dallas, Texas 75202
Cookie - Founders Hall, 1600 N. Calvert Street, Baltimore, MD 21202 – grandpo234@iotaphitheta.org, grandvice567@iotaphitheta.org

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)

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?

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

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

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

Originally Posted by davesexcel

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","Ethel@imail.com"
"Elizabeth","Baca","Lougheed Inc.","482 Alexander Way","Milpitas","Ca","95035","(510)569-8220","elizabethb@hurtmail.net"
"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","wil@harowitz.com"

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)

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.

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 - support@apa1123.net
4	Westgate Services, 2311-24 North Broad Street, Philadelphia, PA 19132-4590 - info@kappaaweqsi1911.com
5	Omega Fraternity, Inc. - 3221 Snapfinger Parkway, Decatur, Georgia 30035 - info@osdpf.org
6	Walstore Inc. - 105 Kennedy Street, NW Washington, DC 20011-5294 - info@ibfghsigma1914.org
7	Sigma, Inc - 7022 Commerce Street, Suite 620, Dallas, Texas 75202
8	Cookie - Founders Hall, 1600 N. Calvert Street, Baltimore, MD 21202 – grandpo234@iotaphitheta.org, grandvice567@iotaphitheta.org

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	support@apa1123.net
4	Westgate Services	2311-24 North Broad Street	Philadelphia	PA	19132-4590	info@kappaaweqsi1911.com
5	Omega Fraternity Inc.	3221 Snapfinger Parkway	Decatur	Georgia	30035	info@osdpf.org
6	Walstore Inc.	105 Kennedy Street	NW Washington	DC	20011-5294	info@ibfghsigma1914.org
7	Sigma Inc	7022 Commerce Street	Dallas	Texas	75202
8	Cookie - Founders Hall	1600 N. Calvert Street	Baltimore	MD	21202	grandpo234@iotaphitheta.org, grandvice567@iotaphitheta.org

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 ****** )

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:.......
........
_ .....................

Originally Posted by acvegas

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!!)

Originally Posted by acvegas

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

Originally Posted by acvegas

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

@ 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

@ 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 your 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	support@apa1123.net
4	Westgate Services, 2311	24 North Broad Street	Philadelphia	PA	19132-4590	info@kappaaweqsi1911.com
5	Omega Fraternity, Inc.	3221 Snapfinger Parkway	Decatur	GA	30035	info@osdpf.org
6	Walstore Inc.	105 Kennedy Street NW Washington DC			20011-5294	info@ibfghsigma1914.org
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	grandvice567@iotaphitheta.org

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 )

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.

Hi jindon,

Originally Posted by jindon

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