<div>
What does your existing formula return? Full Name which contains the first name, second name and the last name separated by spaces? i.e. full name with three words separated by space? If so, try this... First Name:<pre><code class="code-1 nolinks" id="code-20-43029372-1">=IFERROR(TRIM(MID(SUBSTITUTE(VLOOKUP(E8,HR!A:AL,3,FALSE)," ",REPT(" ",255)),1,255)),"")
</code></pre> Second Name:<pre><code class="code-1 nolinks" id="code-20-43029372-2">=IFERROR(TRIM(MID(SUBSTITUTE(VLOOKUP(E8,HR!A:AL,3,FALSE)," ",REPT(" ",255)),255,255)),"")
</code></pre> Last Name:<pre><code class="code-1 nolinks" id="code-20-43029372-3">=IFERROR(TRIM(MID(SUBSTITUTE(VLOOKUP(E8,HR!A:AL,3,FALSE)," ",REPT(" ",255)),255*2,255)),"")
</code></pre> 
</div>

<div>
Your question isn't clear at all. &nbsp;&quot;I just need the first name and second name and the last name&quot;: from where do you want to get this information? 
 
Are you wanting all three names in one cell or are you wanting a cell with all three names to be split into three different cells? 
 
It would be helpful (toward getting you good responses) if you would provide an example of what cell(s) you are starting with and what cell(s) you want as a result.
</div>

<div>
Dear Subodh Tiwari (Neeraj), and CompProbSolv, 
 
Hello, 
 
I’m very sorry that my question is not clear. 
 
for example: 
 
I have a sheet, it is the Database. the sheet name ( Family ) 
A1 cell has a full name ( Sarah Mike John Bobby Brown ) 
 
I have another sheet, called ( Form ) 
 
I just need to take from A1 shorter name 3 words ( Sarah Mike Brown ) 
 
Thanks a lot.
</div>

<div>
Does it have anything to do with your existing VLookup Formula?=IFERROR(VLOOKUP(E8,HR!A:AL,3,FALSE),"") 
 Why not upload a sample file with some dummy data and manually mock up the desired output you are trying to achieve?
</div>

<div>
Still trying to understand the goal here. &nbsp;Based on your example, it sounds as if you are trying to take information from a cell (A1 in your example) and strip out the first, second, and last words in the cell and place them on another sheet. &nbsp;The separator between the words is a space. &nbsp;Is this correct? &nbsp;If so, that should be fairly easy. &nbsp;The key is finding the first, second, and last space character positions and using string functions to grab the text. 
 
If you'll confirm that my description is correct, I can throw together a formula for you.
</div>

<div>
This kind of problem gets complex when you need to consider suffixes or honorifics like Jr. or Dr., single names like Rihanna, and hyphenated names. For this reason, I suggest using a user-defined function so you can handle the exceptions and a worksheet formula like: 

<pre><code class="code-1 nolinks" id="code-20-43029974-1">=Namer(A2)
</code></pre>
The code for Namer should go in a regular module sheet (just like a recorded macro). Make sure to save the workbook with .xlsm file extension and to enable macros when you open the workbook. 
 
As written, the code assumes there is a comma between the name and honorific. So the following names are all handled: 

<div class="indent">
Rihanna 
John Jones 
John Jacob Grant 
Paul Smith, Jr. 
Mylan Reed Jenkins, MD 
Willa Mae Cather Lewis 
Paul Reed Smith Thompkins-Jenkins, Esq
</div>
 
Note: if you don't want to include the honorific or suffix in the returned value, remove the single quote at the beginning of statement 8. 
 

<pre><code class="code-10 nolinks" id="code-20-43029974-2">Function Namer(sName As String) As String
'Returns the first two given names plus a family name
Dim vNames As Variant
Dim Honorific As String, Name As String

Honorific = Mid(sName, InStr(1, sName &amp; &quot;,&quot;, &quot;,&quot;))
Name = Replace(sName, Honorific, &quot;&quot;)
'Honorific = &quot;&quot;

vNames = Split(Name, &quot; &quot;)
Select Case UBound(vNames)
Case Is &lt; 2
 Namer = Name &amp; Honorific
Case Is &gt;= 2
 Namer = vNames(0) &amp; &quot; &quot; &amp; vNames(1) &amp; &quot; &quot; &amp; vNames(UBound(vNames)) &amp; Honorific
End Select
End Function
</code></pre>
</div>

<div>
This is an excellent example of how the path to a solution has to start with a clear description of the goal. &nbsp;I don't think we have that yet.
</div>

Excel & VBA Expert

Subodh Tiwari (Neeraj)

<div>
Dear CompProbSolv, 
Yes exactly this what I mean.
</div>

mb4ever

Name-Shortener.xlsm

<div>
<div class="content wysiwyg-content">
Hello, 
Thank you all for all your efforts. 
Please I want to ask about Excel. I want to have a name in a cell. but I just need the first name and second name and the last name. How to do it? 
 
I am using this code to get the name .. I need to make the name just 3 words. 
=IFERROR(VLOOKUP(E8,HR!A:AL,3,FALSE),&quot;&quot;)
</div>
</div>

Hello,
Thank you all for all your efforts.
Please I want to ask about Excel. I want to have a name in a cell. but I just need the first name and second name and the last name. How to do it?

I am using this code to get the name .. I need to make the name just 3 words.
=IFERROR(VLOOKUP(E8,HR!A:AL,3,FALSE),"")

Excel formula three words in a cell.

Microsoft applications include a variety of software programs, including development and digital authoring programs (Expression and Media Center), educational programs, Internet software, including Essentials, Skype and the Live family, anti-virus, productivity applications and suites like Office, Excel, Word, Outlook, Access and PowerPoint, video games and server applications such as Exchange, SharePoint, IIS and Virtual Server.

Microsoft Applications

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.