Let's start with a simple example:
Here we have a name list.
We need First name as output in Column E.
Let's start with a simple example:
1. Using Text-to-Columns
Step 1: First of all we will select the data, which we want to delimit.
![](https://2.bp.blogspot.com/-LbXnK5YEi7c/WIqwESV-w4I/AAAAAAAAALo/TeE1N4xQlFgVherU2XJOVkruwvN-LrPugCLcB/s1600/5.jpg)
Step 2: Then, go to Data tab in excel and select Text to Columns.
You will this pop-up window.
![](https://4.bp.blogspot.com/-bPmlpF0HQTo/WIqxxfK0rxI/AAAAAAAAAL8/vicJKfnN458GfAusdOFAvxboGbLJh5f4ACLcB/s320/4.jpg)
Step 3: Click on next.
Step 4: Check on Space and click on next. Tab is selected automatically.
Step 5: Change the destination, where you want output.
Select the Last name in Data preview, which you do not want in output.
Check on "Do no import column [skip].
Click on Finish
Your output is here.
2. Using Function
Will start with same example:
If you see in Cell E2, I have typed Formula in E2: "=LEFT(D2,FIND(" ",D2)-1)"
Now, what is the reason behind typing 1 in formula?
We will understand in next Image.
If you see, I have new formula where I haven't mention "-1". Length of the text is 7, which includes "Space" as well. That's the reason we put "-1" to remove space.
3. Using User Defined Function (UDF)
We will start with same example.
Here we will create our own defined function using Macro.
Here we will create our own defined function using Macro.
Step 1: Press Alt+F11
You will see this window (VBE).
Go to Insert and click on Module.
Step 2: Type above mentioned code or Copy & Paste from here.
Function FirstName(Rng As Range)
Spce = WorksheetFunction.Find(" ", Rng)
x = ""
For i = 1 To Spce - 1
Vlue = Mid(Rng, i, 1)
x = x & Vlue
Next i
FirstName = x
End Function
Your Defined function is ready : FirstName
Step 3: Type your function in Cell E2.
You will get your desired output.
Thanks for learning, will come soon with new post.
To learn better please do the same for Last name and post in comments below.
To learn better please do the same for Last name and post in comments below.
You may give your valuable feedback below.
Thanks for defining different and easy step to find first name from name cell.
ReplyDeleteThanks for defining different and easy step to find first name from name cell.
ReplyDeleteThanks #Anuj, will come soon with different blog...
DeleteNice article. Keep it up.
ReplyDeleteThanks #Vikash, will come soon with different blog...
DeleteNice article. Keep it up.
ReplyDeleteGood
ReplyDeleteThanks #Kishan..Keep visiting to learn more.
Delete