hi there

i have a range of 1000 rows where it states city and "sales rep"

i want in cell B1 to show remove "Sales Rep" from the cell. below are some examples of the data points:
Ottawa Sales Rep = "Ottawa"
Sacramento Sales Rep = "Sacramento"
Long Island Sales Rep = "Long Island"

can someone pls help me with a formula? as u can see there are instances where some cities have two words i.e. Long Island

thanks alot!