gregfthompson
asked on
Separate name and suburb to allow list sort by suburb
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.
I seek to have the suburb moved to the next cell in the row for each listing.
Put this formula in B1 and copy down as needed:
=MID(A1,FIND("-",A1)+2,999 )
Kevin
=MID(A1,FIND("-",A1)+2,999
Kevin
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
=IF(ISERROR(FIND(" - ",A1)),"",MID(A1,FIND(" - ",A1)+3,999))
Kevin
ASKER
Thank you...can you then remove the suburb from the first column?
Sure.
Q-26942254.xlsx
Q-26942254.xlsx
ASKER
Dear Kevin,
Or run a similar formula to leave the name in a column?
Thanks very much!
GHreg
Or run a similar formula to leave the name in a column?
Thanks very much!
GHreg
See attached.
Q-26942254.xlsx
Q-26942254.xlsx
ASKER
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
Can you provide the formula because I will have several more of these lists in the next couple of weeks?
Thanks again,
Greg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Immediate and brilliant.
Thank you!
Thank you!
ASKER