Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • 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 HensonIT & Database AssistantCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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