I'm hoping to find someone willing to help with this problem:

I have an Excel file with several thousand records that are in the following format (this represents the contents of a single cell):

System Acct: 123456 Name: 10 FRIENDS OF MONKEYS Credit Limit: 5.00
BusSys Acct: 123456 Address1: 534 SW WEST AVENUE STE 3 Balance: 5.00
Rate Code: P01L Address2: Aging Bucket: 0
City: YORKTOWN Cus Grp: PT Personal Transient
ST/Zip: OR, 97777 Assigned Sales: 999 Unassigned Salesperson
Phone: 530-947-1000 Tear#: 0 Proof#: 0 Aff#: 0
Notes:

What I need to accomplish is to extract the "Name", "Address1", "City", "ST/Zip" and "Phone" into another unique cell or set of cells.

I spent some time poking around the web and found a way, using "FIND" and "MID", to extract each one separately but I don't know how to combine all of the functions and have the results end up in a separate single cell (we want to create a "call list" or mailing list from the results). Here's what I used to get the "Address1" value:

=MID(A11,FIND("Address1:",A11)+10,FIND("Balance:", A11,FIND("Address1:",A11)+1)-46-FIND(" ",A11))

Anyone go ta slick way to accomplish the above? Many thanks for anyone willing to lend a mind

To do this properly, you should output each of Name, Address1, Address2, City, ST/Zip & Phone to separate columns. That way the data can be used for label/list generation via a Word mailmerge. Ideally, you'd do the text-to-columns processing for these data with a macro for all data fields. For the fields you're interested in, though, you can do it with formulae, thus:
Name =MID(A1,SEARCH("Name:",A1)+6,SEARCH("Credit Limit:",A1)-SEARCH("name:",A1)-6)
Address1 =MID(A1,SEARCH("Address1:",A1)+10,SEARCH("Balance: ",A1)-SEARCH("Address1:",A1)-10)
Address2 =MID(A1,SEARCH("Address2:",A1)+10,SEARCH("Aging Bucket:",A1)-SEARCH("Address2:",A1)-10)
City =MID(A1,SEARCH("City:",A1)+6,SEARCH("Cus Grp:",A1)-SEARCH("City:",A1)-6)
ST/Zip =MID(A1,SEARCH("ST/Zip:",A1)+8,SEARCH("Assigned Sales:",A1)-SEARCH("ST/Zip:",A1)-8)
Phone =MID(A1,SEARCH("Phone:",A1)+7,SEARCH("Tear#:",A1)-SEARCH("Phone:",A1)-7)
where the cell to be processed is A1.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Ok this is possible to do with formulas but it will require a lot of them in different columns and it just sounds like a pain. You mentioned you were looking for a slick way to accomplish the above. I cant think of anything slicker than pushing a button and having it all magically happen.

That is what the attached workbook does. I wrote some VBA code to parse out the information and then I have a very simple concatenating formula putting together the name address and phone number. This will work with any number of records and you can even add columns if you need to.

I tested it with the data you provided and it appears to work well. If there are any issues let me know and I will sort them out.

You will have to excuse the old workbook format I have my reasons for using an older version lol.

Thanks for the opportunity to provide solutions.

Attached Files

Address Parse.xls (32.5 KB, 17 views)

My apologies macropod I posted right after you because I did not refresh my page and didnt see any replies. I did not mean to highjack your solution.

No offence taken. As you'll see from my post, I too believe a macro solution (that extracts all the data) would be preferable. I also believe the data should be exported to different columns for each element.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Great! Thanks so much!

WOW! What a nice surprise to find this early in the morning! Thank you both!

Excelled Software, I opened your file and ran the script... then realized I'd made a major blunder. The data is not in a single cell as I had described. I think in the process of playing with the data I placed it in a single cell and did not refer back to the original file when I posted my question. I'm so sorry. The data is actually in separate rows. Needless to say I'm embarrassed for having you spend the time on this. Sorry.

I'm going to see if I can decipher what you provided to get it to work.

I appreciate your time and willingness to help, both of you!

Always better to post a real sheet ( click "Go advanced" and " manage attachments"). Everybody gains

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I'll remember that, thanks. I did get this to work and I really do appreciate the help in getting in done.

Fantastic to hear. I'm glad it worked out.