Request assistance with a formula to pull only the first name from a cell
Currently I have a form which helps me capture the First and Last name of the user among other information. The first and last name is captured in separate columns.
However, 1% of the user inadvertently enters their full name in the first name section. The way I am trying to get around this issue is to have another column to capture the first name and eliminate the additional details wherever present.
I managed to extract the cells which has a space after the first name, as below.
=LEFT([First Name]@row, FIND(" ", [First Name]@row) - 0)
However, I am not sure how to also capture the first names which are accurately entered in the same column.
Hoping the above makes sense.
Thank you
Best Answer
-
Assuming that people use a space " " when they include their last name, you can search the entry for the presence of the space " ". If one is found, then the user has provided something in addition to a first name, so use your expression to parse the entry for the user's first name. If no space is found, then the user has provided a first name only.
The structure of the formula would look like this...
IF( expression_looking_for_space , expression_parsing_for_FirstName , [First Name]@row )
When the user has provided first and last names, the expression FIND(" ", [First Name]@row) will return the position of the space in the string/text--which will not be zero
<> 0
.= IF( FIND( " ", [First Name]@row) <> 0 , LEFT([First Name]@row , FIND(" ", [First Name]@row) - 0) , [First Name]@row )
Answers
-
Assuming that people use a space " " when they include their last name, you can search the entry for the presence of the space " ". If one is found, then the user has provided something in addition to a first name, so use your expression to parse the entry for the user's first name. If no space is found, then the user has provided a first name only.
The structure of the formula would look like this...
IF( expression_looking_for_space , expression_parsing_for_FirstName , [First Name]@row )
When the user has provided first and last names, the expression FIND(" ", [First Name]@row) will return the position of the space in the string/text--which will not be zero
<> 0
.= IF( FIND( " ", [First Name]@row) <> 0 , LEFT([First Name]@row , FIND(" ", [First Name]@row) - 0) , [First Name]@row )
-
Thank you Toufong. The solution worked well. Also, thank you for taking the time to explain the rationale behind the formula.
-
This is precisely the solution I was looking for. Thank you👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!