Page 3 of 3 FirstFirst 123
Results 21 to 27 of 27

Spliting Middle and Last Names Issue

This is a discussion on Spliting Middle and Last Names Issue within the Excel Questions forums, part of the Question Forums category; AlKey, if you look at my table in post 11, it isn't working there either...

  1. #21
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Spliting Middle and Last Names Issue

    AlKey, if you look at my table in post 11, it isn't working there either

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  2. #22
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    284

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by FDibbins View Post
    AlKey, if you look at my table in post 11, it isn't working there either
    I think I know what the problem is. The formula should should enter two spaces if there are two words but I think the forum software compressed it to one. I made some changes to my last formula by adding REPT(" ",2) instead of " " . see post 20

    Excel 2016 (Windows) 32 bit
    ABCD
    1ann edwards sarahannedwardssarah
    2yolanda m. darayolandam.dara
    3herman lopezhermanlopez
    4miike z. boromiikez.boro
    5jen yannajenyanna

    Sheet3



    Last edited by AlKey; Oct 8th, 2016 at 09:30 PM.

  3. #23
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Spliting Middle and Last Names Issue

    So what does the final formula look like?

    (still wont improve the accuracy inherent in the overall process though)

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  4. #24
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    284

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by FDibbins View Post
    So what does the final formula look like?

    (still wont improve the accuracy inherent in the overall process though)
    the last formula in post #20
    If you mean names like where the last two words are the last name? No. This can't be done.
    Betty Jo Spickerman
    Eddie Van Halen

  5. #25
    Board Regular bencar's Avatar
    Join Date
    Jun 2016
    Posts
    65

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by AlKey View Post
    All formulas worked find for me. This would be the last try.
    =TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," ",REPT(" ",2)),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))
    Excellent!!! That works.. Thank you so much for your patience and diligence.

    One last question though: how were you able to paste that small par tof the excel sheet on here?

  6. #26
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    284

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by bencar View Post
    Excellent!!! That works.. Thank you so much for your patience and diligence.

    One last question though: how were you able to paste that small par tof the excel sheet on here?
    You can download it from Excel forums | Excel Matters where you read (Forum Tools) that you can download here.

  7. #27
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    4,989

    Default Re: Spliting Middle and Last Names Issue

    bencar, you seem now to have got to the stage of ignoring that, however good AlKey's formula is, it will NOT give you 100% accuracy where the middle term could be either the 2nd name or part of the last name

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

Page 3 of 3 FirstFirst 123

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com