How to Split the addresses

[niladri20052006]

Hi All,

I am facing one challenges when I am going to split the addresses as Street City State and Postal code wise. I have the addresses in one cell with three Line like below:

12 Daniel Rd East, 3rd Floor
Fairfield, NJ 07004

12647 Alcosta Blvd., STE 165
Bishop Ranch Business Park 15
San Ramon, CA 94583

How will i get these in one line like

12 Daniel Rd East, 3rd Floor Fairfield, NJ 07004

if I get these in one column then it will be better for me to put a comma then I will split these addresses with TEXT to column.

Is there any any way to get these addresses in one line?

Thanks in advance!

 

[hiker95]

niladri20052006,


Sample data before the macro:

Excel Workbook
	A	B
1	12 Daniel Rd East, 3rd FloorFairfield, NJ 07004
2	12647 Alcosta Blvd., STE 165Bishop Ranch Business Park 15San Ramon, CA 94583
3
Sheet1

After the macro:

Excel Workbook
	A	B
1	12 Daniel Rd East, 3rd FloorFairfield, NJ 07004	12 Daniel Rd East, 3rd Floor, Fairfield, NJ 07004
2	12647 Alcosta Blvd., STE 165Bishop Ranch Business Park 15San Ramon, CA 94583	12647 Alcosta Blvd., STE 165, Bishop Ranch Business Park 15, San Ramon, CA 94583
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 07/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=565212
Dim c As Range, Sp
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  Sp = Split(c, Chr(10))
  c.Offset(, 1).Value = Join(Sp, ", ")
Next c
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[AlphaFrog]

You could try to use the Find\Replace feature (Ctrl+H). This might not work depending on the nature of your text, but it's easy enough to try.

Select the column with the addresses
Select from the menu Edit\ Replace (Ctrl+H)

• Find what: Alt+0010
• Replace with: < comma & space >
• Replace All

Alt+0010 is hold down the Alt key and on the number pad type in 0010. This is the ASCII code for Line Feed.
< comma & space > is an actual comma and space.

If 0010 doesn't work, do the same thing using 0013 ASCII code for Carriage Return.

 

[niladri20052006]

Hi

Thanks for your reply.

Is it possible to get this as Street, City, State and Postal code wise.

After running the code I am getting the result

12 Daniel Rd East, 3rd Floor, Fairfield, NJ 07004

But it possible to get like below

12 Daniel Rd East, 3rd Floor, Fairfield, NJ, 07004

so that it will help me to split the addresses with TEXT to Column.

Thanks

Niladri

 

[AlphaFrog]

Just do a 2nd Text-to-Columns split on the State-Zip column using a space as the delimiter.

 

[hiker95]

niladri20052006,


Sample data before the updated macro:

Excel Workbook
	A	B	C	D	E	F
1	12 Daniel Rd East, 3rd FloorFairfield, NJ 07004
2	12647 Alcosta Blvd., STE 165Bishop Ranch Business Park 15San Ramon, CA 94583
3
Sheet1

After the updated macro:

Excel Workbook
	A	B	C	D	E	F
1	12 Daniel Rd East, 3rd FloorFairfield, NJ 07004	12 Daniel Rd East, 3rd Floor		Fairfield	NJ	07004
2	12647 Alcosta Blvd., STE 165Bishop Ranch Business Park 15San Ramon, CA 94583	12647 Alcosta Blvd., STE 165	Bishop Ranch Business Park 15	San Ramon	CA	94583
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV2()
' hiker95, 07/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=565212
Dim c As Range, Sp, Sp1, H As String, L As String
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  Sp = Split(c, Chr(10))
  c.Offset(, 1).Value = Sp(0)
  If UBound(Sp) = 1 Then
    H = Sp(1)
    L = Trim(Right(Application.Substitute(H, " ", Application.Rept(" ", 100)), 100))
    Sp(1) = Left(H, Len(H) - (Len(L) + 1)) & ", " & L
    Sp1 = Split(Sp(1), ", ")
    c.Offset(, 3).Resize(, UBound(Sp1) + 1).Value = Sp1
  ElseIf UBound(Sp) = 2 Then
    c.Offset(, 2).Value = Sp(1)
    H = Sp(2)
    L = Trim(Right(Application.Substitute(H, " ", Application.Rept(" ", 100)), 100))
    Sp(UBound(Sp)) = Left(H, Len(H) - (Len(L) + 1)) & ", " & L
    Sp1 = Split(Sp(2), ", ")
    c.Offset(, 3).Resize(, UBound(Sp) + 1).Value = Sp1
  End If
Next c
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.


If your raw data contains more than 3 rows in one cell, then please give us an example.