You are here:

Excel/Separating text - problem with spaces


JHD wrote at 2010-11-19 23:19:31
This solution does not work if you have anything other than a first and last name. If your name were John Q. Smith, this solution would return Q. Smith as the last name.  Another solution found elsewhere on the Internet suggested this very complicated formula, which seems to work:

=MID(A1,FIND(CHAR(22),SUBSTITUTE(A1," ",CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

Even this is not a perfect solution where items added after the last name (e.g. Esquire, Jr., Sr., etc.)

Anonymous wrote at 2010-12-24 17:45:03
If your getting a #VALUE response after searching for the location of a character, it means that the character isn't there.   This is a common problem when data is exported from an outside source.  

In your case, there's some other whitespace character --not the normal 'spacebar character'.  They look the same to you and I, but Excel knows the difference.  That's why it tells you that it can't find a value.  As far as Excel's concerned, the character you're looking for isn't there.  

1.  First choose one of the cells you're trying to search.

2.  Then select a sample space from in between two words.

3.  Next copy your selection into the clipboard buffer. (Ctrl-C, Cmd-C, or Right-click and 'Copy')

4.  Now call up the Replacement Dialog Box (Ctrl-H, Cmd-H)

5.  And paste your buffer selection into the 'Find' field. (Ctrl-V, Cmd-V, or Right-click and 'Paste')

6.  Select the 'Replace' field, and press your spacebar one time.

7.  Finally press the 'Replace All' button.

Excel replace each non-standard whitespace with the normal spacebar character.  

Once the non-standard whitespace is out and a regular space is in, the formula will work as you expect it to; the #Value errors will go away.  Easy-peasy.  Good Luck!  

Carol W wrote at 2013-02-17 23:07:59
Actually, you do not need to separate the functions, you just need to all parens round the LEN-SEARCH :

=RIGHT(A2,(LEN(A2)-SEARCH(" ",A2)))

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Rost


I am the author of The Complete Idiot's Guide to Excel 2010.
I am happy to answer any questions about Microsoft Excel. If you have an Excel problem, let me help you with it. Also, please be sure to check the Excel Tips & Tricks and Excel Tutorials sections of my web site.
You can also watch my complete Excel 2010 For Beginners tutorial online which is over 90 minutes of FREE videos and an eBook.


I have been using Microsoft Excel since the very early Windows 3.1 versions. I have been teaching Microsoft Excel in the classroom since 1994, and online through computer tutorials since 2002.

I have been volunteering on AllExperts in the categories of Microsoft Access, Visual Basic, and Weight Loss for years.

I am the author of The Complete Idiot's Guide to Excel 2010. I have created a line of computer tutorials online at and

Personally, I am self taught. I've learned everything I know from books and trial & error. If I don't know the answer, I know how to find it.

Past/Present Clients
I have over 20,000 happy clients worldwide.

©2017 All rights reserved.