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

1. ## 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")

2. ## 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?

3. ## 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.

4. ## 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. ## 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. ## 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. ## Re: Right function, Left function, Mid function to extract values in column A

OK - let me have another look.

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

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

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. ## Re: Right function, Left function, Mid function to extract values in column A

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

10. ## 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.

11. ## 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)

12. ## 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. ## Re: Right function, Left function, Mid function to extract values in column A

Okay. I understand your frustration. Thanks.

14. ## 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. ## Re: Right function, Left function, Mid function to extract values in column A

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

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

D2
Formula:
`Please Login or Register  to view this content.`

16. ## 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. ## 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. ## 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. ## 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:
`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:
`Please Login or Register  to view this content.`

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

#### 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 (?)
Template Usage (35):
• (2)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (19)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (5)postbit_attachment
• (19)postbit_legacy
• (19)postbit_onlinestatus
• (19)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
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
• ./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_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_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• 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