Split address code not working

[RAJESH1960]

Hello formula experts
I have this code which splits the address from 1 cell to 4 in the adjacent cells. It was working in an earlier application but when I tried to change and edit the range compatible for this application it is not splitting the data. I would really appreciate it, if someone could please help me to correct the code.
Split address.xlsm

 

[dave3009]

I doubt anyone will be overly keen to download a macro-enabled workbook, Office blocks macros by default requiring the user to go through various steps to unblock them, it's a bit of a faff. You might get a better response if you post the code along with an example of your inputs and outputs (both expected and actual).

You can try xl2bb to create HTML versions of your sheets - XL2BB - Excel Range to BBCode
VBA code tags are in the WYSIWYG editor.

 

[johnnyL]

I doubt anyone will be overly keen to download a macro-enabled workbook, Office blocks macros by default requiring the user to go through various steps to unblock them, it's a bit of a faff. You might get a better response if you post the code along with an example of your inputs and outputs (both expected and actual).

@dave3009 you are not the first to make that recommendation to @RAJESH1960.

...
I have this code which splits the address from 1 cell to 4 in the adjacent cells. It was working in an earlier application but when I tried to change and edit the range compatible for this application it is not splitting the data. I would really appreciate it, if someone could please help me to correct the code.

@RAJESH1960 I think you need to be more specific about what happens when you try to run the code that you altered.

 

[RAJESH1960]

With the help of the code I am trying to split the address into 4 rows.

Split address.xlsm
	P	Q	R	S	T
1	Address	Line1	Line2	Line3	Line4
2	PLOT NO 477-78, SECTOR 29 PART 2, ,, HUDA, PANIPAT
3	Flat No. 2,1 to 2,8, ,, Vikrant 4, RAJENDRA PLACE, Opposite Padam Tower, RAJENDRA PLACE
4	FARM NO. 79A, KH-86, ,, ,, GREEN DRIVE, SILVER OAK FARM, NEW DELHI
5	JAIPUR AIR PORT,SANGANER, ,, ,, JAIPUR,14,RAJASTHAN, Jaipur
6	CAS, ,, CIVIL AIRPORT SANGENAR, SANGENAR, JAIPUR
7	JAIPUR AIRPORT, ,, ,, TERMINAL 2, JAIPUR
8	1489, ,, ,, KAPTAN JI KI GALI 12 GANGOUR KA RASTA, JOHARI BAZAR
9	B-121, ,, ,, SECTOR-5, NOIDA
10	VIJAY MANDI, ,, MURADNAGAR, GHAZIABAD, GHAZIABAD
11	13 - PLOT NO. 63-69, 2ND FLOOR, ,, NAGDEVI CROSS ROAD, MUMBAI
12	65/A, GROUND FLOOR, KANCHAWALA BUILDING, DHANJI STREET, MUMBADEVI
13	17, ,, MAHAVIR INDS. BLDG.,, SUN MILL COMPOUND,, LOWER PAREL,
14	PLOT NO 61, ,, MAROL CO-OPERATIVE INDL ESTATE, SIR MAKVANA ROD, MAROL, ANDHERI EAST
15	A-216, ,, SAGAR TECH PLAZA CHS LTD, ANDHERI KURLA ROAD SAKINAKA JUNCTION, MUMBAI
16	39, 3rd FLOOR, ROOM NO 3,3, ,, OLD HANUMAN LANE, KALBADEVI
17	GALA NO. 126, 1ST FLOOR, SONAL LINK INDUSTRIAL PREMISES CS LTD, LINK ROAD, OPP. MOVIE TIMES THEATRE, MALAD WEST
18	NO.182, ,, ,, KAMARAJ ROAD, BHARATHINAGAR
19	SHED NO 37/1, ,, NADAKERAPPA INDUSTRIAL ESTATE, ANDRAHALLI MAIN ROAD PEENYA 2ND STAGE, VISWANEEDAM POST
20	NO 1, 2ND FLOOR, VST VISTAS, PALACE CROSS ROAD, OPP PALACE GROUND
21	22/1, ,, ,, GODOWN STREET, K.R.MARKET
22	NO.14, ,, ,, S J P ROAD, BENGALURU
23	Plot No. 285, ,, Rajadhani Industrial Park, Road no. 7, KIADB 4th Phase Dabaspet Industrial Area Billenkote Village
24	No.1, 1st and 2nd Floor, ,, 14th Cross, Sampige Road, Malleshwaram, Bangalore
25	ROOM NO. 32 and 38, A25, ,, AIRSIDE BUILDING DEVENAHALLI, BANGALORE INTERNATIONAL AIRPORT, Bangalore
26	REGD AND HEAD OFFICE, ,, POST BOX- 599, MAHAVEERA CIRCLE, KANKANADY, MANGALORE
27	BENGALURU INTERNATIONAL AIRPORT, ,4-181, ,, DEVANHALLI, BANGLORE
28	NO.12, GROUND AND FIRST FLOOR, ,, 3RD CROSS,, H.SIDDAIAH ROAD
29	21/511/B 8,/4B, ,, ,, VALAGEREHALLI, KENGERI HOBLI, MYSORE ROAD
30	NO16/2/3, ,, ,, ARAKERE GATE, B G ROAD
31	NO.71/1,, ,, ,, 1ST MAIN,, NEWTIMBER YARD LAYOUT
32	no.57/3, ,, ,, SADAR PATRAPPA ROAD, BEHIND S J PARK ROAD
33	NO. 339, ,, ,, 1,TH CROSS,, MAHALAKSHMI LAYOUT
34	No 1618, ,, ,, East End Main Road, Jayanagar 9th Block
35	NO. 12,6, FIRST FLOOR, ,, 26TH MAIN, JAYANAGAR 9TH BLOCK
36	No. 148, 2nd floor, Om Shree Plaza, Dr. DVG Road, BASAVANAGUDI
37	NO.1/1, LNP CORNER, ,, WEST OF CHORD ROAD MANJUNATHNAGAR, RAJAJINAGAR BENGALURU
38	163/A, GROUND AND 1ST FLOOR, -, S KARIYAPPA ROAD, TATA SILK FARM LAYOUT, BASAVANGUDI
39	NO 42 43 44 45, ., ., SAJJANI RAO ROAD OLD WARD NO 5,, VV PURAM
40	NO 11, 6TH AND 7TH FLOOR, DIVYASREE CHAMBERS, O SHAUGHNESSY ROAD, Akkithimanahalli
41	OLD NO 19 NEW NO 27, ,, ,, S J P ROAD, S J P ROAD
42	NO 47, ,, ,, DEVINAGAR MAIN ROAD OUTER RING ROAD, MARUTHI NAGAR
43	No.24, ,, ,, 9th Cross H Siddaiah Road, Bangalore
44	NO 134/2, GROUND FLOOR, ,, KILARI ROAD, BANGALORE
45	NO 14, ,, ,, RT STREET, CHICKPET
46	19/1, 3RD CROSS, CHAWLA MOTORS, JOURNALIST COLONY, BANGALORE
47	2, 2nd floor, ,, 1st A Main Vigna Nagar, BANGALORE
48	NO 16/2, ,, ,, AREKERE, BANNERGHATTA MAIN ROAD
49	339/1, Industrial Shed No. 2 Ground Floor, Anantharamiah Woolen Factory Compound, Mysore Road, Bapujinagar
50	1, ,, SRI MAHALAKSHMI FRAME WORKS, CHICKPET, CHITRADURGA
51	NO.26/A,, GROUND,FIRST,SECOND FLOOR,, ,, 15 CROSS,1,, FEET ROAD,, SARAKKI 4TH PHASE EXTENSION,JP NAGAR 4TH PHASE,
52	26/B, GROUND,FIRST AND SECOND FLOOR, ,, 15 CROSS 1,, FEET ROAD SAARAKKI 4TH PHASE EXTENTION, J P NAGAR-4
53	SY NO 127,, Ground floor, Near Prakurthi Chandana Apartment, Kashi nagar ,, Amruthalli
54	NO.16, GROUND FLOOR, R.D.COMPLEX, VICTORY AVENUE , HOSPITAL ROAD, AVENUE ROAD CROSS
55	321, ,, ,, 3RD MAIN, 4TH CROSS, JP NAGAR 3RD PHASE, BANGALORE
56	No 19/1, Ground Floor and First Fllor, ,, 1st cross, 2nd Main, Nagendra Garden, Srirampuram, Bengaluru
57	NO.126, ,, ,, NEAR CHURCH, R T NAGAR POST, BANGALORE
58	No.712/66, ,, ,, 19th Cross, Srirampura, Dr. Shivaramkaranth Nagar, MCECHS Layout, Yelahanka Hobli
59	NO 173, GROUND FLOOR, ,, 1ST MAIN 2ND CROSS OPP SAR CONVENTION HALL, CHANDANA LAYOUT SUNKADAKATTE
60	3, 45/1,, ,, ,, HESARGHATTA MAIN ROAD, NEXT TO BANGALORE PATIDAR SAMAJ, RAJA REDDY LAYOUT, BAGALAKUNTE, BANGALORE
61	no.61, 16th cross, ,, nagarbhavi 2nd stage, bangalore
62	SY.NO.177/6A, ,, ,, BEGUR HOBLI, BILEKAHALLI VILLAGE
63	Sy No 6/3B, ,, ,, 2nd Cross Begur Road, Bengaluru
64	44/M, GROUND FLOOR, ,, SAI BABA TEMPLE ROAD, AREKERE
65	NO.71/1,, ,, ,, NEW TIMBER YARD LAYOUT,, MYSORE ROAD,
66	NO 3,,/3B, GROUND FLOOR, SWETHA NILAYA NEXT TO BSNL EXCHANGE BUILDING, BEGUR MAIN ROAD, BEGUR
67	No 1364, Shop No ,1, Ground Floor, Susana Complex, South End Main Road, 28th Main, 9th Block, Jayanagar, Bengaluru
68	NO.12/1, GROUND FLOOR, ,, 3RD CROSS, SEPPINGS ROAD, NEAR OM SHAKTHI TEMPLE, SHIVAJINAGAR
69	57, ,, PUTTENAHALLI PALYA, PUTTENAHALLI MAIN ROAD, J.P NAGAR 7TH PHASE
70	Site No.13/14, Katha No.62/2,, ,, ,, 1st Floor, 2nd Cross, Kodichikkanahalli Main Road,, Bommanahalli, Begur Hobli,
71	SY NO 9/5 AND 9/6, ,, ,, VITTASANDRA BEGUR POST BEGUR HOBLI, BANGALORE
72	SY NO OLD.6,,NEW NO 6,/4,, ,, ,, KACHOHALLI, MAGADI MAIN ROAD
73	NO.159,NEW NO.144, ,, ,, EAST END,A MAIN ROAD,9TH BLOCK, JAYANAGAR
74	No 1, GROUND FLOOR, ,, BIC SARRAKI GATE KANAKAPURA MAIN ROAD, J P NAGAR 1ST PHASE NEAR METRO STATION
75	New No. 51, 9th Floor, Exotica, Venkatanarayana Road, Chennai
76	58, ,, ,, PARK ROAD, ERODE
77	15-8-441, ,, ,, FEELKHANA, BEGUMBAZAR
MasterData

The code I am trying to use but the range seems wrong.

Option Explicit

Sub Split_Address()
    Dim c, a, l&
    Dim Data, Ledger, Chk, i As Long
    Dim J, k, n, ar, nChar, xstr
    Dim t()     As String
    Dim arr()
    Dim ws1     As Worksheet
'
    Set ws1 = Worksheets("MasterData")
'
    With ws1
        ar = .[A1].CurrentRegion    'row number..?
    End With
'
    ReDim Preserve arr(1 To UBound(ar, 1), 1 To 16)
'
    k = 1
    nChar = 30              'Restricts the number of characters in a cell up to total 120 characters, can edit if required in future
'
    For i = 2 To UBound(ar, 1)
        If ar(i, 16) = "" Then GoTo nexti  ' 5 is the full address in column P
        t = Split(ar(i, 16), ",")
        xstr = t(0)
        n = 1
        nChar = 20
'
        For J = 1 To UBound(t)
            t(J) = Trim(t(J))
'
            If t(J) <> "" Then
                If Len(xstr & t(J)) <= nChar Then
                    xstr = xstr & " " & t(J)
                Else
'                   ReDim Preserve arr(1 To 4, 1 To n)
                    arr(k, n) = Trim(xstr)
                    xstr = t(J)
                    n = n + 1
'
                    If n = 4 Then nChar = 100
                End If
            End If
        Next J
'
        If arr(k, n) = "" Then arr(k, n) = Trim(xstr)   'removes special characters and trims to fit 30 characters in each column
nexti:
        k = k + 1
    Next i
'
    ws1.[F2].Resize(UBound(arr, 1), 6) = arr        'destination first cell where data is split
'
    ws1.UsedRange.EntireColumn.AutoFit                                                          '   Set all used columns on sheet wide enough for data
End Sub

 

[RAJESH1960]

I doubt anyone will be overly keen to download a macro-enabled workbook, Office blocks macros by default requiring the user to go through various steps to unblock them, it's a bit of a faff. You might get a better response if you post the code along with an example of your inputs and outputs (both expected and actual).

You can try xl2bb to create HTML versions of your sheets - XL2BB - Excel Range to BBCode
VBA code tags are in the WYSIWYG editor.

I was also facing the same issue. You have to just right click on the excel sheet name and unblock to enable the macro. Sometimes there are so many sheets involved, XL2BB does not accept as it is limited to some bytes only.

 

[johnnyL]

With the help of the code I am trying to split the address into 4 rows.

We have had this discussion before also ...
rows are vertical
columns are horizontal

The code I am trying to use but the range seems wrong.

Explain, what range you are referring to & why you feel it seems wrong.

When you post questions, please remember that we can't see what you are looking at, so please be as specific as possible about what sheet you are talking about, what address, etc.

You might get a better response if you post the code along with an example of your inputs and outputs (both expected and actual).

@RAJESH1960 Surely that should sound familiar. I know we have had that same discussion previously.

 

[RAJESH1960]

' Split_Address
'
    Set ws1 = Worksheets("MasterData")
'
    With ws1
        ar = .[A1].CurrentRegion    'row number..?
    End With
'
    ReDim Preserve arr(1 To UBound(ar, 1), 1 To 6)
'
    k = 1
    nChar = 30              'Restricts the number of characters in a cell up to total 120 characters, can edit if required in future
'
    For i = 2 To UBound(ar, 1)
        If ar(i, 5) = "" Then GoTo nexti  ' 5 is the full address in column E
        t = Split(ar(i, 5), ",")
        xstr = t(0)
        n = 1
        nChar = 20
'
        For J = 1 To UBound(t)
            t(J) = Trim(t(J))
'
            If t(J) <> "" Then
                If Len(xstr & t(J)) <= nChar Then
                    xstr = xstr & " " & t(J)
                Else
'                   ReDim Preserve arr(1 To 4, 1 To n)
                    arr(k, n) = Trim(xstr)
                    xstr = t(J)
                    n = n + 1
'
                    If n = 4 Then nChar = 100
                End If
            End If
        Next J
'
        If arr(k, n) = "" Then arr(k, n) = Trim(xstr)   'removes special characters and trims to fit 30 characters in each column
nexti:
        k = k + 1
    Next i
'
    ws1.[F2].Resize(UBound(arr, 1), 6) = arr        'destination first cell where data is split
'
    ws1.UsedRange.EntireColumn.AutoFit                                                          '   Set all used columns on sheet wide enough for data
End Sub

This is the working code of the previous application. This time the address is in a different column and the count of columns is also different. So, I tried editing the ar and arr but .... as there are no comments it is very difficult to understand and edit...

 

[RAJESH1960]

04. SPD Import Purchases with masters 27072022.xlsm
	E	F	G	H	I
1	ADDRESS	Split 1	Split 2	Split 3	Split 4
2	263 Kamraj Road, Bangalore - 560042	263 Kamraj Road	Bangalore - 560042
3	318/7 Near Ram Mandir, Sethi Chowk, Panipat	318/7 Near Ram Mandir	Sethi Chowk Panipat
4	UNIT NO 242, 2 ND FLOOR, BUILDING NO 5B, MITTAL ESTATE, ANDHERI(E)	UNIT NO 242	2 ND FLOOR	BUILDING NO 5B	MITTAL ESTATE ANDHERI(E)
MasterData

Solution was correct. This is the result of the old app.

 

[johnnyL]

Previous results don't hold much weight. It would be easier if you post the expected results from the script code in this thread.

 

[RAJESH1960]

Like this -The trim is very important as there are multiple ,, in the characters.

Split address.xlsm
	P	Q	R	S	T
1	Address	Line1	Line2	Line3	Line4
2	PLOT NO 477-78, SECTOR 29 PART 2, ,, HUDA, PANIPAT	PLOT NO 477-78	SECTOR 29 PART 2	HUDA, PANIPAT
3	Flat No. 2,1 to 2,8, ,, Vikrant 4, RAJENDRA PLACE, Opposite Padam Tower, RAJENDRA PLACE	Flat No. 2,1 to 2,8	Vikrant 4, RAJENDRA PLACE	Opposite Padam Tower	RAJENDRA PLACE
4	FARM NO. 79A, KH-86, ,, ,, GREEN DRIVE, SILVER OAK FARM, NEW DELHI	FARM NO. 79A	KH-86, GREEN DRIVE	SILVER OAK FARM	NEW DELHI
MasterData