Separate name and suburb to allow list sort by suburb

gregfthompson
gregfthompson used Ask the Experts™
on
I have a list of 4000  entries each with a name with suburb in one cell. Suburb follows a dash.
I seek to have the suburb moved to the next cell in the row for each listing.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
Put this formula in B1 and copy down as needed:

=MID(A1,FIND("-",A1)+2,999)

Kevin
Top Expert 2008

Commented:
Handles odd cases where there is no suburb or there is a dash in the first part.

=IF(ISERROR(FIND(" - ",A1)),"",MID(A1,FIND(" - ",A1)+3,999))

Kevin
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Top Expert 2008

Commented:
I might as well as post the revised workbook ;-)

Kevin
Q-26942254.xlsx

Author

Commented:
Thank you...can you then remove the suburb from the first column?
Top Expert 2008

Commented:

Author

Commented:
Dear Kevin,

Or run a similar formula to leave the name in a column?

Thanks very much!

GHreg
Top Expert 2008

Commented:
See attached.
Q-26942254.xlsx

Author

Commented:
Thanks Kevin,

Can you provide the formula because I will have several more of these lists in the next couple of weeks?

Thanks again,

Greg
Top Expert 2008
Commented:
I put this in column C:

=IF(LEN(B1)=0,A1,LEFT(A1,LEN(A1)-LEN(B1)-3)

I then copied columns B and C and pasted values only. Then I cut column C and pasted it into column A.

Kevin

Author

Commented:
Immediate and brilliant.

Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial