split cell with Proper vs UPPERCASE

[JohnLoughridgq]

Is it possible to split the following data into separate cells

Sample:
1 Acacia Ave LAKE MUNMORAH
1 Ace Cres TUGGERAH

EG: "1 Acacia Ave", "LAKE MUNMORAH"
EG: "1 Ace Cres", "TUGGERAH"

All my sample data has the suburb in UPPERCASE

 

[JohnLoughridgq]

attached as requested

 

[Eloise T]

I believe using an UPPERCASE formula, is a good angle of attack.
Insert a "working" column so the formula can pull the data into this new column (since Excel pulls data into cells rather than pushing it.)

 

[JohnLoughridgq]

I would like to split the "ADDRESS" field into "address" and "suburb"
EG:
"12 Stewart Street" and "DAINTREE"
"McColl Road" and "GIRGARRE"
"115 Albany Highway" and "VICTORIA PARK"
"PO Box 2083" and "PORT AUGUSTA"
"45 Sydney Road" and "MANLY"

I think possibly the only way to do it is use an UPPERCASE formula ?

 

[JohnLoughridgq]

I believe using an UPPERCASE formula, is a good angle of attack.
Insert a "working" column so the formula can pull the data into this new column (since Excel pulls data into cells rather than pushing it.)

any ideas of a formula that might work ?

 

[Eloise T]

I suggest inserting two side-by-side blank columns. The first column will be the address and the second column will be the suburb.

Due to the PO (post office) box possibilities, have the formula begin at the right side of the address string and work from right to left until it no longer finds an UPPER case and "save" that as the suburb in the 2nd column. The remaining string is saved as the address in the first column.

The above is easier said than done...for me. I did come up with the attached spreadsheet using LEFT and RIGHT, LEN and FIND functions, BUT the only problem is you have to have a delimiting comma. See attachment.

One other option is to highlight the ADDRESS column, click on the DATA tab. Select Text to Columns, and use the text to columns wizard by following the prompts.
...OR...
click on the DATA tab, Select From Text (import a text file) and follow the prompts.

 

[AhmedAbbas]

Hi Mate,
Had a quick look at your excel sheet, there is a small irregularity in your data, All addresses are supposed to have a Street Number and for PO Box you can leave it the way it is, Once you fixed that, than just simply select the address column>>Data>>Text to Column>>Delimited>>Next>>Delimiters(Select Space)>>Next>>Finish>>Press Ok>>Your Columns will be from C:G>>Insert One Column in front of Column E >> Paste this formula =C2&" "&D2&" "&E2(This becomes your address>> Now same Insert a column infront of Column H and type this formula =G2&" "&H2 this becomes your Suburb>> Last step copy Both Columns and Paste them as Value>> Now its upto you if you want to delete obsolete columns or not, Once in practice should not take you more than 2 mins to do it. Hope it helps

 

[bosco_yip]

I would like to split the "ADDRESS" field into "address" and "suburb", EG:
"12 Stewart Street" and "DAINTREE"
"McColl Road" and "GIRGARRE"
"115 Albany Highway" and "VICTORIA PARK"
"PO Box 2083" and "PORT AUGUSTA"
"45 Sydney Road" and "MANLY"

1] Formula way, without adding comma as a delimiter.

2] See attached file.

Regards

 

[Nebu]

Hi:

If you are interested in VBA

Sub test()
Sub test()
Application.ScreenUpdating = False

Dim ref As RegExp
Set reg = New RegExp

With reg
.Pattern = "[A-Z]{3,}"
.Global = True
End With

For i& = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    With Sheet1
    Str1 = .Cells(i, 1)
    .Cells(i, 4) = reg.Replace(Str1, "")
    .Cells(i, 5) = Trim(Right(.Cells(i, 1), Len(.Cells(i, 1)) - Len(.Cells(i, 4)) + 1))
    End With
Next

Application.ScreenUpdating = True
End Sub

Note:

Use the following steps to add the reference library before running the code
To use in Excel follow these steps :

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

• Select "Developer" tab
• Select "Visual Basic" icon from 'Code' ribbon section
• In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
• Select "References"
• Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
• Click "OK"

Thanks

 

[shrivallabha]

Hi:

If you are interested in VBA

Sub test()
Sub test()
Application.ScreenUpdating = False

Dim ref As RegExp
Set reg = New RegExp

With reg
.Pattern = "[A-Z]{3,}"
.Global = True
End With

For i& = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    With Sheet1
    Str1 = .Cells(i, 1)
    .Cells(i, 4) = reg.Replace(Str1, "")
    .Cells(i, 5) = Trim(Right(.Cells(i, 1), Len(.Cells(i, 1)) - Len(.Cells(i, 4)) + 1))
    End With
Next

Application.ScreenUpdating = True
End Sub

Note:

Use the following steps to add the reference library before running the code
To use in Excel follow these steps :

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

• Select "Developer" tab
• Select "Visual Basic" icon from 'Code' ribbon section
• In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
• Select "References"
• Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
• Click "OK"

Thanks

References part can be skipped if early binding is switched to late.

Sub test()
Application.ScreenUpdating = False

Dim ref As Object 'RegExp
'Set reg = new regexp
Set reg = CreateObject("VBScript.RegExp")

With reg
.Pattern = "[A-Z]{3,}"
.Global = True
End With

For i& = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    With Sheet1
    Str1 = .Cells(i, 1)
    .Cells(i, 4) = reg.Replace(Str1, "")
    .Cells(i, 5) = Trim(Right(.Cells(i, 1), Len(.Cells(i, 1)) - Len(.Cells(i, 4)) + 1))
    End With
Next

Set reg = Nothing
Application.ScreenUpdating = True
End Sub