+ Reply to Thread
Results 1 to 11 of 11

Formula to split Text

Hybrid View

  1. 11-23-2014, 01:17 AM #1
    Taisir
    Taisir is offline
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Formula to split Text

    Dear friends
    Please, I need a couple of Functions to separate the Last Names and First Initials from a list of thousands of names. (see attached sample)

    Basically,

    For last name, I need a function that examine the cell and delete the last characters (initials) AFTER the first space appear from right.
    For initials, I need a function that examine the cell and delete the all the characters (Last name) Before the first space appear from right.

    Or any other formula that can do the job

    Please see attached sample

    Many thanks in advance Taisir
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-23-2014, 01:34 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,956

    Re: Formula to split Text

    for the last name...
    =LEFT(A3,FIND(" ",A3,1)-1)
    for the initials...
    =MID(A3,FIND(" ",A3,1)+1,99)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 11-23-2014, 01:45 AM #3
    Taisir
    Taisir is offline
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Formula to split Text

    Dear Fdibbins
    Thanks for the quick and prompt reply.

    The formulas did not produce the intended results.
    Can you please apply it to the attachment and view the last five entries
    just realized that even entries 2, 3 and 4 not as intended.


    all the best
    Last edited by Taisir; 11-23-2014 at 01:50 AM.
    Reply With QuoteRegister To Reply
  4. 11-23-2014, 02:00 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,956

    Re: Formula to split Text

    My apologies, I did not notice the others had more than 1 space.

    For the last name...
    D3=LEFT(A3,FIND("@@",SUBSTITUTE(A3," ","@@",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))),1)-1)
    for the initials
    E3=MID(A3,LEN(D3)+2,99)
    Reply With QuoteRegister To Reply
  5. 11-23-2014, 02:05 AM #5
    Taisir
    Taisir is offline
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Formula to split Text

    Dear Fdibbins
    Thanks again.
    The formula for the Last Name worked perfect

    The formula for the first initial did not. It reproduced the original entry

    Many thanks again
    Taisir
    Reply With QuoteRegister To Reply
  6. 11-23-2014, 02:08 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,956

    Re: Formula to split Text

    See below where I used both of my formulas...

    A
    B
    C
    D
    E
    1
    Original List
    Desired Outcome
    My Outcome
    2
    Last
    Initials
    Last
    Initials
    3
    		 ABO-ELENEEN RE ABO-ELENEEN RE ABO-ELENEEN RE
    4
    		 ABD EL-MOEZ SI ABD EL-MOEZ SI ABD EL-MOEZ SI
    5
    		 ABD EL-BASSET MA ABD EL-BASSET MA ABD EL-BASSET MA
    6
    		 ABD EL WAHED A ABD EL WAHED A ABD EL WAHED A
    7
    		 ABDALLA MS ABDALLA MS ABDALLA MS
    8
    		 AB RAHIM MH AB RAHIM MH AB RAHIM MH
    9
    		 AB RAHMAN AHMED NNN AB RAHMAN AHMED NNN AB RAHMAN AHMED NNN
    10
    		 AB KADIR MO AB KADIR MO AB KADIR MO
    11
    		 AL QURAISHY NADER S AL QURAISHY NADER S AL QURAISHY NADER S
    12
    		 AL OTHMAN ZA AL OTHMAN ZA AL OTHMAN ZA


    D3=LEFT(A3,FIND("@@",SUBSTITUTE(A3," ","@@",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))),1)-1)
    E3=MID(A3,LEN(D3)+2,99)
    You will notice that my 2nd formula is based on my 1st formula
    Reply With QuoteRegister To Reply
  7. 11-23-2014, 02:15 AM #7
    Taisir
    Taisir is offline
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Formula to split Text

    Dear Fdibbins
    Thanks again.
    Perfect and excuse my ignorance.
    I need to figure out now how to indicate that my question is solved
    Reply With QuoteRegister To Reply
  8. 11-23-2014, 02:11 AM #8
    ELeGault
    ELeGault is offline
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Formula to split Text

    C3 = Initials
    =RIGHT(A3,IFERROR(FIND(" ",RIGHT($A3,2)),IFERROR(FIND(" ",RIGHT(A3,3))+1,3)))

    B3 = Last (Requires Initials, or you could embed the entire initials formula over C3 in the below formula)
    =LEFT(A3,(LEN(A3)-LEN(C3)-1))

    If you are trying this in D and E to ensure they are working it would be the same initials just change C3 in the LAST formula to look at E3
    Last edited by ELeGault; 11-23-2014 at 02:13 AM.
    -If you think you are done, Start over - ELeGault
    Reply With QuoteRegister To Reply
  9. 11-23-2014, 02:25 AM #9
    Taisir
    Taisir is offline
    Forum Contributor
    Join Date
    10-06-2013
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: Formula to split Text

    Dear ELeGault
    Many thanks.

    How to adjust your formula to work with Column A only.
    Please note that Fdibbins provided good solution, but would be interested to examine yours
    Reply With QuoteRegister To Reply
  10. 11-23-2014, 02:22 AM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,956

    Re: Formula to split Text

    you'r welcome. To mark the thread as "solved" using the thread tools just above your 1st post
    Reply With QuoteRegister To Reply
  11. 11-23-2014, 02:28 AM #11
    ELeGault
    ELeGault is offline
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Formula to split Text

    Like I said... take the C3 and replace with initials formula

    C3 = Initials
    =RIGHT(A3,IFERROR(FIND(" ",RIGHT($A3,2)),IFERROR(FIND(" ",RIGHT(A3,3))+1,3)))

    B3 = Last
    =LEFT(A3,(LEN(A3)-LEN(RIGHT(A3,IFERROR(FIND(" ",RIGHT($A3,2)),IFERROR(FIND(" ",RIGHT(A3,3))+1,3))))-1))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using 'Text to columns' when value to split is derived from a formula
    By Aysha in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2014, 06:49 AM
  2. [SOLVED] Formula to Split up string of text
    By kenadams378 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2013, 05:43 AM
  3. Formula To Split Text at a Certain Point
    By steiney in forum Excel General
    Replies: 2
    Last Post: 04-07-2011, 12:55 PM
  4. Split one cell into two by formula not text to colums function
    By robfink2.0 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2010, 02:28 AM
  5. [SOLVED] Formula to split Text from Numbers in cell
    By John Ortt in forum Excel General
    Replies: 2
    Last Post: 06-07-2006, 06:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1