Pau Lo
asked on
address data clean up
I've received a batch of data in a single column in an excel spreadsheet.
It should have been spread across 5 columns, e.g. address1, address2, address3, address4, postcode. But its been provided in a single column, e.g. "123 EE Street, EE Suburb, EE Town, EE County, EE21 76F"
Ideally the last 'segment' (anything after the last comma in the text string), should always be the postcode, so I was wondering how you would approach extracting everything after the last comma in a cell, into a column of my choosing.
And then how would you get a count of how many comma's there are in a single cell, to determine how many address segments there are in the cell? As assuming its always under 5, I can simply use text to columns to split those. But I wanted to get the postcode segment extracted away before I try that as I think there may be a few more odd entries.
It should have been spread across 5 columns, e.g. address1, address2, address3, address4, postcode. But its been provided in a single column, e.g. "123 EE Street, EE Suburb, EE Town, EE County, EE21 76F"
Ideally the last 'segment' (anything after the last comma in the text string), should always be the postcode, so I was wondering how you would approach extracting everything after the last comma in a cell, into a column of my choosing.
And then how would you get a count of how many comma's there are in a single cell, to determine how many address segments there are in the cell? As assuming its always under 5, I can simply use text to columns to split those. But I wanted to get the postcode segment extracted away before I try that as I think there may be a few more odd entries.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I usually create a VBA routine for that. It is easiest, and efficient, to use the Split() function to return the count of a delimiter. In your case, the delimiter is a comma character.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Using the Split() function, you can create a VBA function to return any part of the split string.
Public Function Q_29198418_GetPart(ByVal parmCellText, ByVal parmPart = 0)
Q_29198418_GetPart = Split(parmCellText, ",")(parmPart)
End Function
ASKER