+ Reply to Thread
Results 1 to 10 of 10

To get First word, second word

  1. #1
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    132

    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.

    Please assist.
    Last edited by lalaarif1; 05-04-2018 at 03:04 AM.

  2. #2
    Valued Forum Contributor kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016/Work 2013 Pro Plus
    Posts
    1,345

    Re: To get First word, second word

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

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,595

    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.
    Glenn



  4. #4
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    132

    Re: To get First word, second word

    PFA the file for the reference.

    Please assist.
    Attached Files Attached Files

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,595

    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))
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    3,045

    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))

  7. #7
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    132

    Re: To get First word, second word

    Thanks a lot

    You made my day.

  8. #8
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    132

    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. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    3,045

    Re: To get First word, second word

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

  10. #10
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    132

    Re: To get First word, second word

    Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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