+ 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
    268

    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
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    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 Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    Re: To get First word, second word

    PFA the file for the reference.

    Please assist.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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
    2016
    Posts
    5,891

    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
    268

    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
    268

    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
    2016
    Posts
    5,891

    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
    268

    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)

Similar Threads

  1. Extract Word header table data to excel (closed word document)
    By Yakov on Excel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2017, 02:12 PM
  2. Excel VBA - Word to search for part of a word and replace full word
    By charlie543 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-24-2016, 02:56 AM
  3. Extract a word in the workbook's filename and add this word to another word
    By cyberjoad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2016, 03:54 AM
  4. [SOLVED] (Challenge) Sum Order From Count "Center" Word To Start Word & "Center" Word To End Word
    By sanju2323 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-31-2015, 12:58 AM
  5. [SOLVED] Excel macro- Find replace an itallic font word with a regular word?
    By thisisaboutwork in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2013, 01:48 PM
  6. Copy Many Word Docs to New Word Doc (Each Word Doc Should be on New Page)
    By realniceguy5000 in forum Word Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2013, 03:28 AM
  7. Replies: 0
    Last Post: 06-15-2011, 02:22 PM

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