I am using the below formula to separate First and Last names from one column into two because the Text to Columns isn't working either for some reason. However, this formula only seems to be working for the first two rows and I get #VALUE! when I attempt to copy it down. Any help is much appreciated.
=LEFT(A1,FIND(" ",A1)-1)
Can you post a sample workbook that demonstrates the issue?
Maybe other words don't have space within cell?
Never use Merged Cells in Excel
in changing the sample around to be posted, the cells that i physically typed names into worked but the last two were pasted into the worksheet and they do not work. maybe there's a formatting problem with the pasted cells?
What looks like a space in rows 7/8 is a non-breaking space.
Do find and replace, and in the find box, press and hold the Alt key, and type 0160 on the NUMERIC keypad. In the replace box, enter a space. Replace all.
Entia non sunt multiplicanda sine necessitate
Thank you shg. I didn't know there was something called a "non-breaking" space.
Is a webpage the source of the text? The "spaces" that don't evaluate are
actually HTML non-breaking spaces.
Try this before on the data:
Alternatively, you could use this formula:
Edit.Replace Find What: [Alt]+0160....That's: Hold down [Alt]…type 0160…release [Alt] Replace with: (press the spacebar one time) Click: [Replace All]
Does that help?C1: =LEFT(A1,FIND(" ",SUBSTITUTE(A1,CHAR(160)," "))-1)
I got it working. Thank you all. If either of you can advise me on how I can avoid non-breaking spaces in the future, I'd appreciate it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks