Excel Formula To Separate First And Last Name With Space Working With Text in Excel

You are searching about Excel Formula To Separate First And Last Name With Space, today we will share with you article about Excel Formula To Separate First And Last Name With Space was compiled and edited by our team from many sources on the internet. Hope this article on the topic Excel Formula To Separate First And Last Name With Space is useful to you.

Working With Text in Excel

Working with text in Excel is important. In general, Excel worksheets contain not only numbers, formulas and functions, but also text. For example, if we enter data about staff or students in a class, we would certainly enter names, addresses, phone numbers, email addresses, etc. We should be able to manipulate such data. Suppose we have names entered in an Excel sheet as full names and we need to separate the full names into first and last names. Or we have email addresses and we want to match those addresses with data in our database. Sometimes the first and last names may be in separate cells in an Excel worksheet, and we may need to match the first and last names. How can we do such text manipulations in Excel? The features we should be aware of are:

‘=LEFT(text, number of characters from the left)’

Example: We want to separate the first name from the full name, for example “Tom Hawkins”. We would use a formula like ‘=LEFT(B1,3)’ where B1 is the Excel cell that contains the name. Similarly we can extract last name using ‘=RIGHT(B1,7)’. So far so good. But what happens if we have a list of names like: “Tom Hawkins”, “Hari Malhotra” and “Dick Chenney”.

We would have to write each formula three times to complete the text extraction! Is there an easier way out? Yes!

This is where the ‘length’ and ‘find’ functions come into play. The ‘=length(text)’ function finds the number of characters in a name. The function ‘=find(text_in_text’,text)’ finds, for example, a ‘space’ between the first and last name. Then by properly combining the above four functions ‘left, right’, ‘find’ and ‘length’, we could easily create formulas to help us retrieve the information without having to repeat the formulas for each name.

To separate first name and last name, we could write the following formulas:

‘=left(text,find(” “,text)-1)’

‘=right((text),(len(text)-find(” “,text)))

The first function separates characters up to a space from the left between first and last name. Since a space is also included, we subtract 1 to get the correct result.

The second function is a bit more complex. Here we want to get the last name and we extract it from the right. Therefore, we find the length of the name and subtract the integer from the ‘find’ function to finally get the exact number of characters on the right.

There are other interesting and easy-to-use text functions such as ‘average’, ‘correct’, ‘exact’, ‘trim’, concatenation and ‘instr’. Of course, you can create an Excel macro to do the above if you need to perform such manipulations on your Excel worksheet on a regular basis.

Using these strategies and other text functions, you can extract interesting information from your Excel worksheet data!

Video about Excel Formula To Separate First And Last Name With Space

You can see more content about Excel Formula To Separate First And Last Name With Space on our youtube channel: Click Here

Question about Excel Formula To Separate First And Last Name With Space

If you have any questions about Excel Formula To Separate First And Last Name With Space, please let us know, all your questions or suggestions will help us improve in the following articles!

The article Excel Formula To Separate First And Last Name With Space was compiled by me and my team from many sources. If you find the article Excel Formula To Separate First And Last Name With Space helpful to you, please support the team Like or Share!

Rate Articles Excel Formula To Separate First And Last Name With Space

Rate: 4-5 stars
Ratings: 9037
Views: 62733102

Search keywords Excel Formula To Separate First And Last Name With Space

Excel Formula To Separate First And Last Name With Space
way Excel Formula To Separate First And Last Name With Space
tutorial Excel Formula To Separate First And Last Name With Space
Excel Formula To Separate First And Last Name With Space free
#Working #Text #Excel

Source: https://ezinearticles.com/?Working-With-Text-in-Excel&id=1807706

Bài viết đã được tạo 1406

Bài liên quan

Bắt đầu nhập từ khoá bên trên và nhấp enter để tìm kiếm. Nhấn ESC để huỷ.

Trở lên trên