We help IT Professionals succeed at work.
Troubleshooting Question

address data clean up

28 Views
Last Modified: 2020-10-26
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.

Comment
Watch Question

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
That works perfectly, do you have anything to assist with counting how many occurrences of a specific character are in a cell, in this case the comma character.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Brian BEE Topic Advisor, Independent Technology Professional
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Open in new window

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions