split a string in 2 cells in excel

in an excel sheet i have a string in the following format written in a cell:

italy - rome
england - london
france - paris
etc

now i want to split the above string into 2 cells e.g

cell1 cell2
italy rome
france paris
england london

can someone pls remind how i can do it pls in excel, because i remember myself doing it in the past using some sort of Function, but i can't figure out which function i used to use.

thank you!

 

Not sure, but maybe the function you were using was Concat() where you
specify the new cell contents as a concatinated value of another cell.
regards

 

For a one time run, try

Data / Text to Columns

On the 3rd screen of the wizard, you can even specify a new location to put the results into so that you don't need to mess with the original cells.

Have fun.

 

i've managed to achieve it by using this formula:

=LEFT(A1,FIND( "- ", A1)-1) = "italy "

=MID(A1, (FIND( "- ",A1)+2), 10) = "rome "

However, your idea of converting text to columns and using the "-" as the delimeter is much easier.

thank you!

 

Use of MID

One problem with Maxmangion's solution is that if the section after the "-" is greater than 10 characters, the end of the string will be truncated. For example "USA - Washington DC" would return "Washington" without the DC.

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

Would overcome this but is a little unweildy. An alternative would to be to add another column (B perhaps) with

=FIND( "- ",A1)

And then use the cells in that column in the final column

=MID(A1,(B1+2),(LEN(A1)-B1))

 

Use right

Thinking about it, using RIGHT would be easier still

=RIGHT(A1,LEN(A1)-FIND( "- ",A1)-1)