+ Reply to Thread
Results 1 to 19 of 19

Right function, Left function, Mid function to extract values in column A

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Right function, Left function, Mid function to extract values in column A

    Looking for formula (Right function, Left function, or Mid function) to extract values in column A and place initials in column B, middle name in column C and firstname in column D. See attached sample file. Thanks.

    First Logic
    Names without initial should be placed in column D

    Second Logic
    Names with initial:
    1. Place initial in column B (e.g. John A. - A. will be in column B)
    2. Place name in column D (e.g. John A. - John will be in column D)

    Third Logic
    Names with middle name:
    1. Place middle name in column C (e.g. Afsaneh Mashayekhi. - Mashayekhi will be in column C)
    2. Place name in column D (e.g. Afsaneh Mashayekhi. - Afsaneh will be in column D)

    Fourth Logic
    Initial and name in paranthesis:
    1. Place initial in column A (e.g. A. C. (Del). - A. C. will be in column A)
    2. Place name in column D (e.g. A. C. (Del). - (Del) will be in column D)

    Names in this format:
    Name: Bin-Ming (Benjamin) - means no middle name or initial
    Name: Catherine E. (Kate) - initial is E. and first name is Catherine (Kate) - kate in parathesis is alias for Catherine so we want to have it as Catherine (Kate) (same for this type of name Edward E. "Ned")
    Attached Files Attached Files
    Last edited by bjnockle; 06-13-2020 at 10:21 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    I can see a big problem with your requirement, and this is the way you have dealt with entries starting at row 6 - what is the logic you have used that means that the initial here is seen as the middle initial and not the initial of the first name? Unless you can define your logic, we can't replicate it in Excel. Excel is not intelligent to learn human judgement, so can you explain the rule you've used in this sample data?

    Rows 29 and 33 - how would Excel be able to tell any difference her?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Re: Right function, Left function, Mid function to extract values in column A

    AliGW: I can see a big problem with your requirement, and this is the way you have dealt with entries starting at row 6 - answer: meant to say "initial" and not middle initial. Thanks for the catch.

    See updated sample file.
    Attached Files Attached Files
    Last edited by bjnockle; 06-13-2020 at 09:14 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    There are still several anomalies, though. We need some logic - where there are no initials, how should Excel decide where to place the names? Where there are double initials, how should it know whether to treat it as a first name or not?

    Again, we need to know the rules.

    Tell us the thought processes you apply when doing this manually.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    Just to be clear, there are too many unexplained patterns at the moment for me to start helping - I'd have to guess in a lot of cases, and I'd probably guess wrong.

  6. #6
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Re: Right function, Left function, Mid function to extract values in column A

    AliGW: I have revised the file based on your feedback for clearer requirements.
    Note: should be treated as first name in the dataset.
    A.B.
    A.H.
    A.H

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    OK - let me have another look.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    OK - sorry, but there are still more questions than answers!

    Please explain your logic for these rows:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    4
    John A.
    A.
    John
    5
    A
    A
    6
    A. Alexander
    A.
    Alexander
    7
    A. Alexander (Sandy)
    A.
    Alexander (Sandy)
    8
    A. C. (Del) A. C. (Del)
    Sheet: Sheet1 (3)

    Got to go and plant out some runner beans. Back later.

  9. #9
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Re: Right function, Left function, Mid function to extract values in column A

    AliGW: updated file. Sorry for the inconvenience. See sample file.
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    OK - I'm sorry, but I don't have time for this.

    You are expecting ME to go right the way through your dataset and work out the rules that YOU are already applying! NOWHERE have you yet told me what these are, despite my asking now at least three times. I don't have time to back-engineer it all, and I shouldn't even have to be doing this.

    All I wanted you to do is EXPLAIN the logic YOU are applying to decide on where things should go so that I can replicate that in Excel. Now you've added a third results column, but still no explanation. If you can't be bothered to list what those rules (your logic) are, then I'm sorry, but I can't be bothered to try to help you find a solution.

    I'm out.
    Last edited by AliGW; 06-13-2020 at 10:07 AM.

  11. #11
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Re: Right function, Left function, Mid function to extract values in column A

    AliGW: I am writing/explaining the logic. Please standby. Thanks

    First Logic
    Names without initial should be placed in column D

    Second Logic
    Names with initial:
    1. Place initial in column B (e.g. John A. - A. will be in column B)
    2. Place name in column D (e.g. John A. - John will be in column D)

    Third Logic
    Names with middle name:
    1. Place middle name in column C (e.g. Afsaneh Mashayekhi. - Mashayekhi will be in column C)
    2. Place name in column D (e.g. Afsaneh Mashayekhi. - Afsaneh will be in column D)

    Fourth Logic
    Initial and name in paranthesis:
    1. Place initial in column A (e.g. A. C. (Del). - A. C. will be in column A)
    2. Place name in column D (e.g. A. C. (Del). - (Del) will be in column D)

    Names in this format:
    Name: Bin-Ming (Benjamin) - means no middle name or initial
    Name: Catherine E. (Kate) - initial is E. and first name is Catherine (Kate) - kate in parathesis is alias for Catherine so we want to have it as Catherine (Kate)
    Last edited by bjnockle; 06-13-2020 at 10:19 AM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    No, sorry - you have had your window of opportunity with me. Hopefully someone else will step in. It should not have taken this long for you to provide what I've been asking for since post #2 (THREE hours ago)!

  13. #13
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Re: Right function, Left function, Mid function to extract values in column A

    Okay. I understand your frustration. Thanks.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Right function, Left function, Mid function to extract values in column A

    You still need to provide that information for anyone else who wants to help.

    EDIT: I see you you’ve added it to the opening post and lower down.

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,047

    Re: Right function, Left function, Mid function to extract values in column A

    Please try at
    B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bo_Ry; 06-13-2020 at 12:31 PM.

  16. #16
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    889

    Re: Right function, Left function, Mid function to extract values in column A

    Bo_Ry:
    Some of it are not showing the correct results. See wrong sample below.

    Yen should not be a middle name.

    Chung Yen Yen Chung
    Chung Yen (Tom) Yen Chung (Tom)

    The below names should not fall under initial.
    Dr. Albert
    Dr.
    Dr. Charles
    Dr. E.
    Dr.
    Dr. John
    Dr. Lynne
    Dr. Malcolm
    Dr.
    Dr. R.
    Dr. Ralph
    Dr. Randolph
    Dr. W.
    Dr.A.
    Dr.Aart J.
    Dr.Chi-Foon
    Dr.Eli
    Dr.Harry R.
    Dr.Michael J.
    Dr.Sasson

  17. #17
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,047

    Re: Right function, Left function, Mid function to extract values in column A

    Sorry, I have no idea how to put Fox to the middle name without Yen stay with the First name.

    Brian Fox
    Chung Yen
    Chung Yen (Tom)

    The Dr. part is possible, I will try tomorrow.

  18. #18
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    451

    Re: Right function, Left function, Mid function to extract values in column A

    From a database perspective, this is pretty much a case of: Garbage in, garbage out.

    - Anthony Dr. vs. Dr. Albert E.
    - Dr.Michael J. (missing space)
    - Julius W. "Dr. J"

    What exactly is the name pattern/logic here:
    First Name (Standardize) - Middle Name - First Name
    Chung Yen - - Chung Yen
    Chung Yen (Tom) - - Chung Yen (Tom)
    Tom Chung Yen - Chung Yen - Tom

    I highly doubt that formulas will help you here, as you need to make exceptions for apparently wrong/inconsistent database inputs.
    And the next time you make an addition that is another style of wrong input, formulas won't work again.

    I'd suggest you clean up the data first.

  19. #19
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,047

    Re: Right function, Left function, Mid function to extract values in column A

    I can't get all the expected result, This is just for fun.

    B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C2
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,B2,),LEFT(D2,FIND(" ",D2&" ")-1),),MID(D2,FIND(" ",D2&" ")+1,20),))

    D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. [SOLVED] Extract texts from cell using SUBSTITUTE, RIGHT, LEFT, or MID FUNCTION
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2020, 10:12 PM
  2. Extract texts from cell using RIGHT or LEFT FUNCTION
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-10-2020, 09:48 AM
  3. [SOLVED] Extract texts from cell using RIGHT, LEFT OR MID FUNCTION
    By bjnockle in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2020, 01:31 AM
  4. [SOLVED] Using left Function to extract data based on several Criteria
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2019, 11:38 AM
  5. Need VBA function to extract values from one column based on values enlisted
    By suhasg1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2018, 10:54 AM
  6. [SOLVED] How to use the LEFT function to extract just characters
    By sx200n in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2018, 12:15 PM
  7. [SOLVED] Using LEFT function to extract value
    By src16 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2013, 09:37 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.09972 seconds
  • Memory Usage 9,572KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (19)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (5)postbit_attachment
  • (19)postbit_legacy
  • (19)postbit_onlinestatus
  • (19)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./packages/vbforum/bbcodehelper/table.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1