Excel formula

y0ssar1an22

Hopefully this is where this question belongs!

i have a column in excel consisting of the following:

Loser Like Me - Glee
Lost Without You - Freya Ridings
Love Crime - Siouxsie Sioux & Brian Reitzell
Love Don't Cost A Thing - Jennifer Lopez


is there a formula i can use that will move everything after the '-' to the next column, and also delete the '-' from both columns (should it get copied over to the 2nd column)?

thanks

mr_cochise

I think it is called Text to columns. It is a function in excel. It is some time since I have used it.

y0ssar1an22

mr_cochise wrote: »

I think it is called Text to columns. It is a function in excel. It is some time since I have used it.

thats exactly it, and pretty seamless (few little issues).

thanks!

ronivek

Is this just a once off or do you need to use a formula to do it for some reason?

If it's a once off the easiest way would be: https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Jimbob1977

Highlight entire column

Data -> Text to Columns

Pick "Delimited"

Click the following delimiters

o Tab

o Other

In the box beside Other... type in a dash

Next

Finish

That's it

davetherave

If there is only one - in each cell you can do this with the Text to Columns feature, found on the the data tab.

Select the column that you want to split.
Then go to the data tab and select Text to Columns.



Then select deliniated text, and select next.



On that screen you should see options like spaces, tabs, commas and an other box. Put a - in the other box




You can then select finish and should hopefully have split everything at the dash.

smuggler.ie

all as above...
note1: if there is second dash(or whatever delimiter specified) in the same cell, it will break into more columns
note2: spaces either side of delimiter are not removed (further editing needed)