Break of an address in a cell

[drop05]

I’m VBA. Is this possible, If a cell has an address, state, city, and zip code and state in one cell. Is it possible to break that up into separate cells?
So the address is in
cell A1 = 123 Address st, Dallas, Tx, 12345

and I want to copy the address and break it into different cells and paste into sheet2

so sheet 2
A1 is the address: 123 address st
A2 is city: Dallas
A3 is state: Tx
A4 is zip: 12345

 

[kweaver]

You could do text to columns rather than VBA.

There might be a way to insert the array contents straight into a series of columns without the loop.

Sub SplitAddr()
' split address in A1
Dim addr() As String, i As Long
addr = Split(Range("A1"), ",")
 For i = 0 To UBound(addr)
  Cells(1, i + 2) = addr(i)
 Next i
End Sub

 

[Radoslaw Poprawski]

PQ solution

oAAQqn69R4ikZ1sZ6xst_work.csv
	A	B	C
1	Address		Streat
2	123 Address st, Dallas, Tx, 12345		123 Address st
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Address.1", "Address.2", "Address.3", "Address.4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Address.1", "Streat"}, {"Address.2", "City"}, {"Address.3", "State"}, {"Address.4", "Postal code"}})
in
    #"Renamed Columns"

Also @MrExcel in one of his video shows VBA udf function TextSpliter. however i cant find the video now.

 

[kweaver]

Here's another VBA way without a loop:

Sub SplitAddr()
' split address in A1
Dim addr() As String
addr = Split(Range("A1"), ",")
Range("B1:E1") = addr
End Sub

 

[drop05]

You could do text to columns rather than VBA.

There might be a way to insert the array contents straight into a series of columns without the loop.

Sub SplitAddr()
' split address in A1
Dim addr() As String, i As Long
addr = Split(Range("A1"), ",")
 For i = 0 To UBound(addr)
  Cells(1, i + 2) = addr(i)
 Next i
End Sub

I’d like to try the text to column but I am working with two workbooks so not sure if I could use that. Have vba code that transfer the values from a sheet in workbook1 to workbook 2 but some are addresses so thinking of a way to separate

so this code if I’m reading it right. It goes to A1 and splits it until a comma comes across in that cell
Do you mind explaining the for loop a little confused there.

 

[drop05]

PQ solution

oAAQqn69R4ikZ1sZ6xst_work.csv
	A	B	C
1	Address		Streat
2	123 Address st, Dallas, Tx, 12345		123 Address st
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Address.1", "Address.2", "Address.3", "Address.4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Address.1", "Streat"}, {"Address.2", "City"}, {"Address.3", "State"}, {"Address.4", "Postal code"}})
in
    #"Renamed Columns"

Also @MrExcel in one of his video shows VBA udf function TextSpliter. however i cant find the video now.

Man I need to look into learning power quarry that is awesome. No worries but good to know I’ll try to find it
Mainly working with vba because I’m not too familiar with PQ and everything I wrote is in vba

 

[Radoslaw Poprawski]

@drop05
This would be good for PQ since ctrl+alt+f5 refreshes all querries and all the code is re-applied to querry.
you dont have run it every time separetly, but use simple refresh.
I gave you the code from advanced editor, otherwise all the stuff i did i simply clicked my way from the ribbon.
So did not "code it" PQ did it for me.

 

[kweaver]

My last edited post splits it without a loop. The split is done based on a comma, then the array is spread across the four columns.

Sub SplitAddr()
' split address in A1
Dim addr() As String
addr = Split(Range("A1"), ",")
Range("B1:E1") = addr
End Sub

All in 1 line:

Sub SplitAddr()
Range("B1:E1") = Split(Range("A1"), ",")
End Sub

 

[drop05]

@drop05
This would be good for PQ since ctrl+alt+f5 refreshes all querries and all the code is re-applied to querry.
you dont have run it every time separetly, but use simple refresh.

Do you have any recommendations on good videos or websites to look at for learning PQ?

 

[Radoslaw Poprawski]

Tons.
@MrExcel @youtube
@ExcelisFun @youtube.

a lot of videos and tons to gain.