Separating names into 2 columns

[aviva65]

I have been able to separate first/last names from one column to 2 in the past but can no longer, and I don't understand why.  When I have a list of names, some of which might be 2 words, some might have a Mr., some might have an initial etc....I have always been able to separate them so I can have the names in their own columns. One method is using Text to Columns.  Another method is using CTR+H to delete spaces or characters from the field. Now I am no longer able to do any of this unless I have typed the info into the spreadsheet by hand.  I have been copying and pasting lists from other sources for a long time, to sort them in excel and suddenly I cannot do it.  What could have changed? I assume it has to do with how I am pasting it in but I am doing that the same way. Thoughts?

[#2]

@aviva65 

Try the following before trying to split the names:

• Select the column with names.
• Press Ctrl+H to activate the Replace dialog.
• Make sure that Num Lock is on.
• Click in the 'Find what' box and press Alt+0160 using the numeric keypad. this enters a non-breaking space.
• Click in the 'Replace with' box and press the spacebar. This enters an ordinary space.
• Click 'Replace All'.

Do the methods you used previously, such as Data > Text to Columns, work now?

[#3]

Thank you for your reply. I don't have a Num lock key nor an Alt key. I tried the ease of access keyboard to activate Num lock but can't do the same with Alt and enter the numbers at the same time.

EDIT: I figured out substitutes for the keys, but it didnt make a difference in the outcome.

[#4]

@aviva65 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

[#5]

I went back and tried your suggestion again with clearing the data using ALT0160 and it has helped. I am still playing with it, but I thank you for that suggestion!

[#6]

@aviva65 

You may

=SUBSTITUTE(E3, " ", CHAR(160) )

drag it down and split result. Before entering the formula be sure cells are in General format.

[#7]

Thank you!