Delete character and transfer everything to the right

[taichi56]

I have a spreadsheet with 23,000 rows. One column has words seperated by a line, not a hyphen and then more words. I need to be able to delete that line and everything after it moved to the next column beside it. I cannot use Text to columns because other does not let me put that line, just a hyphen.

Example:
Creature — Human Soldier

So column
A: B:
Creature Human Soldier

I attached a spreadsheet with exactly how it is in my original database. As you can see that is not a hyphen. Not sure how they did that. Thank you.

ExcelForum_SeparateCharacter.xlsx

[Harribone]

You can use text to columns. Select Fixed Width to do this (depends on length of first words though, will work for your example data).

If not:
=LEFT(A1,FIND(" ",A1)-1) and =RIGHT(A1,LEN(A1)-FIND(CHAR(151),A1)-1) can be used in Col B and C. You can then copy these two columns and paste special values over itself to retain the data and remove the formulas. Then you can delete Col A.

[Jakobshavn]

The long dash is an ASCII code 151 character. Click on any cell containing that character and hi-light only that character in the Formula Bar.
Touch CNTRL-c
When you run Text-to-Columns, specify Delimited > Other
Click on the little box and touch CNTRL-v to paste the long dash into the box.

This is a good way to get any weird character into Text-to-Columns.

[Harribone]

Ahh good old copy and paste, such an obvious way to approach this! Don't know how I missed that one!

[taichi56]

LOL, some time you overlook the obvious, like I just did. The copy and paste worked wonderfully. Thank you everyone.