# To get First word, second word

1. ## To get First word, second word

Dear Team,

Could you please help me with the comprehensive formula to derive the following output. It will be useful for vlookup formula.

Input
1. Arif Asgar Lala
2. Arif Lala

1st Output required
1. Arif Lala
2. Arif Lala

2nd Output required
1. Arif Asgar
2. Arif Lala

There are thousands of rows to match.

The conditions will vary depending on the requirement of the management.

2. ## Re: To get First word, second word

Attach a workbook with some sample data and the expected result.

3. ## Re: To get First word, second word

We cannot see what is meant to be in each cell.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

4. ## Re: To get First word, second word

PFA the file for the reference.

5. ## Re: To get First word, second word

I am sure that a sample of two will not be representative of your real data..... but try this in B2, copied down:

=LEFT(A2,SEARCH(" ",A2)-1)&" "&TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))

6. ## Re: To get First word, second word

Try:

1.

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",200)),400))

2.

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",200)),200)&RIGHT(SUBSTITUTE(A2," ",REPT(" ",200)),200))

Thanks a lot

8. ## Re: To get First word, second word

1 quick question.

My name is Arif Asgar Khadija Lala.

Can I have the formula which gives the output as Arif Asgar Khadija ?

9. ## Re: To get First word, second word

=TRIM(LEfT(SUBSTITUTE(A2," ",REPT(" ",200)),600))

10. ## Re: To get First word, second word

Thanks a lot.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1