Solved How to Extract Address Data from an Excel Worksheet?

January 20, 2017 at 20:40:55
Specs: Windows 7
Excel 2010

I 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 B
Name: Bob Jones
Address: 123 Main Street
City: Northshore
etc...

How can I extract and arrange the info in columns, like so...?

A B C
Bob Jones 123 Main Street City
etc...

Thanks in advance for your kind assistance.

message edited by SMoran


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

Report •

✔ Best Answer
January 23, 2017 at 13:21:14
As an alternative to Mike's fine suggestion, enter these formula in C2:C5

C2: =B2
C3: =B3
C4: =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:C22
Next, 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 Court
Apollo Beach, FL 33572
(A Blank Cell)

Next:

Select Column C
Press [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 Court
Apollo Beach, FL 33572

AAA Mobile Auto Glass Inc
P O Box 10574
St. Petersburg, FL 33733

etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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.

MIKE

http://www.skeptic.com/

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 C

Put 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.

MIKE

http://www.skeptic.com/


Report •

#5
January 23, 2017 at 13:21:14
✔ Best Answer
As an alternative to Mike's fine suggestion, enter these formula in C2:C5

C2: =B2
C3: =B3
C4: =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:C22
Next, 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 Court
Apollo Beach, FL 33572
(A Blank Cell)

Next:

Select Column C
Press [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 Court
Apollo Beach, FL 33572

AAA Mobile Auto Glass Inc
P O Box 10574
St. Petersburg, FL 33733

etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question