Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

Simplifying Excel Formula

Hi all,

I have an excel formula that takes the contents of a name in column B, then does this:

- Extracts the first letter of the Last name
- Extracts the first letter of the First name
- Extracts the first letter of the Middle name

It'll then attach the date in Column A to create a String, then places it in Column C (please see attached below).

 Screen
Here is the formula:

=iferror(LEFT(B81,1)&MID(B81,FIND(",",B81)+2,1)&LEFT(TRIM(RIGHT(SUBSTITUTE(B81," ",REPT(" ",LEN(B81))),LEN(B81))),1)&TEXT(A81,"mmddyyyy"),"")

Can someone simplify it so that we can disregard the Middle Name altogether? I just want the first char of Last, first char of First, then combine the date.

Thanks in advance.
0
Go-Bruins
Asked:
Go-Bruins
  • 5
  • 5
  • 2
2 Solutions
 
Rob HensonFinance AnalystCommented:
To ignore the Middle Name just remove the section in bold:

=iferror(LEFT(B81,1)&MID(B81,FIND(",",B81)+2,1)&LEFT(TRIM(RIGHT(SUBSTITUTE(B81," ",REPT(" ",LEN(B81))),LEN(B81))),1)&TEXT(A81,"mmddyyyy"),"")

Leaving:
=IFERROR(LEFT(B81,1)&MID(B81,FIND(",",B81)+2,1)&TEXT(A81,"mmddyyyy"),"")
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this....

=IFERROR(LEFT(B81,1)&LEFT(TRIM(RIGHT(SUBSTITUTE(B81," ",REPT(" ",LEN(B81))),LEN(B81))),1)&TEXT(A81,"mmddyyyy"),"")

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Sorry Rob! Didn't refresh the page before posting.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I just want the first char of Last, first char of First, then combine the date.
In that case try this....
=IFERROR(LEFT(TRIM(RIGHT(SUBSTITUTE(B81," ",REPT(" ",LEN(B81))),LEN(B81))),1)&LEFT(B81,1)&TEXT(A81,"mmddyyyy"),"")

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Neeraj - that gives first character of First and First Character of Middle.

Assuming that the text is in format:
Last, First Middle

Sample given was Aardvark, Ben Hur
Result was ABH & Date
Question states
- Extracts the first letter of the Last name  = A from Aardvark
- Extracts the first letter of the First name  = B from Ben
- Extracts the first letter of the Middle name = H from Hur

Therefore new result should be only AB & Date
0
 
Rob HensonFinance AnalystCommented:
In case there are leading spaces or multiple spaces between Last and First, try this:

=IFERROR(LEFT(TRIM(B81),1)&MID(TRIM(B81),FIND(",",TRIM(B81))+2,1)&TEXT(A81,"mmddyyyy"),"")

Thanks
Rob
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Ah.. didn't pay that much attention to it. My bad.
Thanks for pointing this out.
0
 
Rob HensonFinance AnalystCommented:
Maybe a literal translation mistake. Aardvark is indeed the first name in the cell and Hur is the last name in the cell.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Only OP can tell what's the case here. :)
0
 
Go-BruinsAuthor Commented:
Rob - your last formula did the trick. Thanks to both of you!
0
 
Rob HensonFinance AnalystCommented:
Glad to help.

Also good to be a member of EE to enter into discussions over interpretation of requirements and solutions.
0
 
Go-BruinsAuthor Commented:
Just to clarify:

"Aardvark" is the last name.
"Ben" is the first name.
"Hur" is the middle name.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now