I'm looking for a formula to manage song list

Hi. I'm receiving on a regular basis a list of songs with their chords from a college.
The problem that unfortunately he send it in "word" and not in "Excel" and thus it has Inconsistent form and Extra spaces that make it more complicate to copy the list to Excel.
Fom example I get this list:

ערב כחול עמוק/ Cm
בגללך / Em
זמר שכזה/Am

As you can see the spaces between the name of the Chords and the "/" are are Inconsistent and I would like to use some formula In order to arrange it nicely and easier to read.
I would like to split that list into 2 columns: On the first will be the name of the song (in the List it appears in small Letters and its in Hebrew!)
And on the second column will be the Chord name (in the List it appears in very big English Letters)
So far I'm using the following formula to manage it:
=LEFT(A1, LEN(A1)-4)
=RIGHT(A1,2)
But it always require me to make adjustments. For example the song list will look like that in excel:

EXXX.JPG

So as you see at the middle column third line, there should be two words but the second word is incomplete and misses 1 letter at the end if you compare it to the first column
One thing that can help is that normally the song name is in Hebrew and the Chords are in English. So maybe there is a formula that can separate words by language into different columns? hope you can help. Thanks

Hello, try using the text to columns feature. This can be found in the data ribbon at the top. To use this, first make sure there are a few empty rows to the right of the column. Here are the steps:

1. Click text to columns button.
2. Delimited, then click next.
3. Check the "other" box, and put a "/" in the box. Click next.
4. Click Finish.

The output should be with the chord in a separate column next to the song. Hope this helps!

you are the Best! it work's great.

Originally Posted by ag3

Hello, try using the text to columns feature. This can be found in the data ribbon at the top. To use this, first make sure there are a few empty rows to the right of the column. Here are the steps:

1. Click text to columns button.
2. Delimited, then click next.
3. Check the "other" box, and put a "/" in the box. Click next.
4. Click Finish.

The output should be with the chord in a separate column next to the song. Hope this helps!

Thank you!!! you are the Best! work's great, just need to find another formula to delete the irrelevant spaces

Thank you All !!! you really helped me. have a lovely day!

Originally Posted by soundv

Thank you!!! you are the Best! work's great, just need to find another formula to delete the irrelevant spaces

The TRIM function removes leading and trailing spaces.