Solved How to Extract Address Data from an Excel Worksheet?

January 20, 2017 at 20:40:55
Specs: Windows 7
 Excel 2010I have a worksheet with address data arranged as one long list, with the title in Column A and the info I need in Column B, like so...A BName: Bob JonesAddress: 123 Main StreetCity: Northshoreetc...How can I extract and arrange the info in columns, like so...?A B CBob Jones 123 Main Street Cityetc...Thanks in advance for your kind assistance.message edited by SMoran

See More: How to Extract Address Data from an Excel Worksheet?

January 23, 2017 at 13:21:14
 As an alternative to Mike's fine suggestion, enter these formula in C2:C5C2: =B2C3: =B3C4: =MID(A7,FIND(":",A7)+2,LEN(A7))&", "&MID(A8,FIND(":",A8)+1,LEN(A8))&" "&MID(A9,FIND(":",A9)+1,LEN(A9))&" "&MID(A10,FIND(":",A10)+1,LEN(A10))C5: =""`(C4 was stolen from Mike)`Next, select C2:C22Next, grab the lower corner of C22 and drag downward.Assuming (as Mike noted) that each block of data contains the same number of lines, you should get something like this next to each block of data:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572(A Blank Cell)Next:Select Column CPress [F5].In the resulting Go To dialog box, click Special.Click the Blanks option and click OK. Doing so highlights the blank cells in the selected range. Right-Click, Choose Delete, Choose "Shift Cells Up"You should end up with this:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572 AAA Mobile Auto Glass IncP O Box 10574 St. Petersburg, FL 33733etc.message edited by DerbyDad03

#1
January 21, 2017 at 08:48:44
 Is all your data exactly the same, 3 lines, Name, address, city?What does the etc at the bottom mean?Please post several complete samples of your data.MIKEmessage edited by mmcconaghy

Report •

#2
January 21, 2017 at 08:53:56
 Before posting your example data, please click on the following line and read the instructions on how to post example data in this forum. Thanks!

Report •

#3
January 23, 2017 at 09:07:40
 Thanks for the responses. Below is a sample of the data for three vendors as it appears(thanks DerbyDad03). I need to extract Name, Address, City, State and Zip. As another wrinkle, the actual data for Name and Address is in column B, but as you can see the actual data for City, State and Zip appear in column A. Thanks for your help.``` A B 1 Vendor Number: 360510-001 2 Name : 360 Sign Group Inc. 3 Address: 6525 Santiago Court 4 : 5 : : 6 : : 7 City : Apollo Beach City : 8 State : FL State : 9 Country: Country: 10 Zip : 33572 Zip : 11 12 Contact: Liability Insurance 13 Phone : Carrier: 14 Fax # : Policy : 15 E-Mail : Date : 16 17 Terms : 2 Net Days 18 # Days : 30 19 Disc. : 0.000% YTD Payments : 20 Always Take Discount: No 21 - - 22 Vendor Number: AAA340-001 23 Name : AAA Mobile Auto Glass Inc 24 Address: P O Box 10574 25 : 26 : : 27 : : 28 City : St. Petersburg City : 29 State : FL State : 30 Country: Country: 31 Zip : 33733 Zip : 32 33 Contact: Liability Insurance 34 Phone : 727-393-1132 Carrier: 35 Fax # : Policy : 36 E-Mail : Date : 37 38 Terms : 2 Net Days 39 # Days : 30 40 Disc. : 0.000% YTD Payments : 41 Always Take Discount: No 42 - - 43 Vendor Number: AAA510-001 44 Name : AA Action Door Repair, Inc. 45 Address: P. O. Box 92404 46 : 47 : : 48 : : 49 City : Lakeland City : 50 State : FL State : 51 Country: Country: 52 Zip : 33804-2404 Zip : 53 54 Contact: Liability Insurance 55 Phone : 1 800 335 4435 Carrier: 56 Fax # : Policy : 57 E-Mail : Date : 58 59 Terms : 2 Net Days 60 # Days : 30 61 Disc. : 0.000% YTD Payments : 62 Always Take Discount: No 63 - - ```

Report •

Related Solutions

#4
January 23, 2017 at 11:17:03
 I'm not completely sure how you want the data to display.As long as your data remains in the same configuration you posted, this will put Name, Address, City, State, Country & Zip into a single cell, in Column CPut this formula in cell C2 and drag down to the bottom of list.=IF(LEFT(A2,4)="Name",B2&" "&B3&" "&MID(A7,FIND(":",A7)+1,LEN(A7))&", "&MID(A8,FIND(":",A8)+1,LEN(A8))&" "&MID(A9,FIND(":",A9)+1,LEN(A9))&" "&MID(A10,FIND(":",A10)+1,LEN(A10)),"")The formula is a bit long, so you might want to Copy & Paste it from here.You should end up with something like:``` A B C 1) Vendor Number: 360510-001 2) Name : 360 Sign Group Inc. 360 Sign Group Inc. 6525 Santiago Court Apollo Beach, FL 33572 3) Address: 6525 Santiago Court 4) : 5) : : 6) : : 7) City : Apollo Beach City : 8) State : FL State : 9) Country: Country: 10) Zip : 33572 Zip : 11) 12) Contact: Liability Insurance 13) Phone : Carrier: 14) Fax # : Policy : 15) E-Mail : Date : 16) 17) Terms : 2 Net Days 18) # Days : 30 19) Disc. : 0.000% YTD Payments : 20) Always Take Discount: No 21) 22) Vendor Number: AAA340-001 23) Name : AAA Mobile Auto Glass Inc AAA Mobile Auto Glass Inc P O Box 10574 St. Petersburg, FL 33733 24) Address: P O Box 10574 25) : 26) : : 27) : : 28) City : St. Petersburg City : 29) State : FL State : 30) Country: Country: 31) Zip : 33733 Zip : 32) 33) Contact: Liability Insurance 34) Phone : 727-393-1132 Carrier: 35) Fax # : Policy : 36) E-Mail : Date : 37) 38) Terms : 2 Net Days 39) # Days : 30 30) Disc. : 0.000% YTD Payments : 41) Always Take Discount: No 42) - - 43) Vendor Number: AAA510-001 44) Name : AA Action Door Repair, Inc. AA Action Door Repair, Inc. P. O. Box 92404 Lakeland, FL 33804-2404 45) Address: P. O. Box 92404 46) : 47) : : 48) : : 49) City : Lakeland City : 50) State : FL State : 51) Country: Country: 52) Zip : 33804-2404 Zip : 53) 54) Contact: Liability Insurance 55) Phone : 1 800 335 4435 Carrier: 56) Fax # : Policy : 57) E-Mail : Date : 58) 59) Terms : 2 Net Days 60) # Days : 30 61) Disc. : 0.000% YTD Payments : 62) Always Take Discount: No ```See how that works for you.MIKEhttp://www.skeptic.com/

Report •

#5
January 23, 2017 at 13:21:14
 As an alternative to Mike's fine suggestion, enter these formula in C2:C5C2: =B2C3: =B3C4: =MID(A7,FIND(":",A7)+2,LEN(A7))&", "&MID(A8,FIND(":",A8)+1,LEN(A8))&" "&MID(A9,FIND(":",A9)+1,LEN(A9))&" "&MID(A10,FIND(":",A10)+1,LEN(A10))C5: =""`(C4 was stolen from Mike)`Next, select C2:C22Next, grab the lower corner of C22 and drag downward.Assuming (as Mike noted) that each block of data contains the same number of lines, you should get something like this next to each block of data:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572(A Blank Cell)Next:Select Column CPress [F5].In the resulting Go To dialog box, click Special.Click the Blanks option and click OK. Doing so highlights the blank cells in the selected range. Right-Click, Choose Delete, Choose "Shift Cells Up"You should end up with this:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572 AAA Mobile Auto Glass IncP O Box 10574 St. Petersburg, FL 33733etc.message edited by DerbyDad03