I have a cell with first and lastname and I cannot figure out how to seperate them. Irecall doing this once and it didnt’ feel that hard but I must be doing soemthing wrong in the code
sometimes there are multiple space between
ex.
John Doe with code below gives me John
Andrée Dompierre give me Andree Dom !?
=LEFT(D12,LEN(D12)-FIND(" ",D12))
<edited by @Lupp>
I changed the tag “math” to “calc”.
</edit>
You could use Data - Text to columns and specify the space as the separator.
Note that this works well for “John Smith” but you will end up with three columns for “Daphne de Maurier”
[Edit]
Also look out for “Jean Paul Gaultier” with two first names
Not surprising. You find " " directly after the first name. Why do you subtract from length of string?
Your formula will remove from the right the number of characters used for the first name on left…
So start with removing LEN(D12)- from your formula and check the result. You may need to add -1
You tagged math which means you have a problem with the math editor used to insert nicely formatted formulas in text. But the example you show rather suggests an issue with Calc (spreadsheet). In this case, retag calc.
I know this is no fair cut, but it seems fitting…
.
To make something useful from this:
When developing this case of formulas I start with separate columns for
=LEN(D12) and =FIND(" ",D12)) so I can see the intermediate results and adapt the final formula.
The following formulas work just with one space between names; so, before you will need to find spaces ( *) and replace with space ( ).
First name: =LEFT(D12;FIND(" ";D12)-1)
Last name: =MID(D12;FIND(" ";D12)+1;LEN(D12))
This is dynamic, but if do you need a once for all action, follow @EarnestAl advice.
If splitting names seems straightforward, this previous question mentions and links to many pitfalls, split names into separate columns
See also:
The appended text is not by me. I don’t know for what reason nowadays a link can’t be simply a link , and from my understanding the text doesn’t describe the linked page well.
Might somebody write a good essay about the spilling of useless “eye-catching” in the web (and everywhere).