Heello, I'm trying to remove every second and/or third name of every person in my excel file. Now i'm doing it manually (and it' s very boring). Is there any formula to to it automatically?
For example I have cell A1 that contains "Filippo Luigi Carlo", is possible to convert it to only "Filippo" ?
deleting every second and third(if the person has it) name in every cell of an excell file
CodePudding user response：
How about:
=left(A1,find(" ",A1,1)-1)
I assumed that the first name is in cell A1, alter if not.
Based on your comment about "pier" then:
IF(IFERROR(FIND("pier",LEFT(A1,4),1),0)=1,A1,LEFT(A1,FIND(" ",A1,1)-1))
CodePudding user response：
You will want to use the following:
=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)
What does it do?
- Finds the first space in your target text
FIND(" ", A1) - Takes all the characters from the start of your target text up until the spot before the first space
LEFT(A1, {step 1} - 1) - If there is an error (for example there is no space in your target text), it will simply return the original target text
=IFERROR({step 2}, A1)
Limitations:
- The first name cannot include a space (example:
Anne Marie Joneswill returnAnneinstead ofAnne Marie) - Any other errors will simply return the original target text
CodePudding user response：
joining the party, I assume you want to keep the first name and the last name. The first name is given by @solar-Mike above. For the last name, unfortunately excel only has formulae that search or find left to right, so more than two names are a problem. This site seems to give a good solution: a reverse find function
The formula for the last name is horrendous: =IF( ISERROR( FIND(" ",A1) ),A1,RIGHT(A1, LEN(A1)-FIND("~", SUBSTITUTE(A1," ","~", LEN(A1)-LEN(SUBSTITUTE(A1," ","")) ) ) ) )
Tack on the bit to get the first name, and you get:
=left(A1,find(" ",A1,1)-1) & " " & IF( ISERROR( FIND(" ",A1) ),A1,RIGHT(A1, LEN(A1)-FIND("~", SUBSTITUTE(A1," ","~", LEN(A1)-LEN(SUBSTITUTE(A1," ","")) ) ) ) )
That will drop all the middle names.