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

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

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.

0
Go-Bruins
• 5
• 5
• 2
2 Solutions

Finance 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

Excel & VBA ExpertCommented:
Try this....

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

Excel & VBA ExpertCommented:
Sorry Rob! Didn't refresh the page before posting.
0

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"),"")
``````
0

Finance 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

Finance 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

Excel & VBA ExpertCommented:
Ah.. didn't pay that much attention to it. My bad.
Thanks for pointing this out.
0

Finance 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

Excel & VBA ExpertCommented:
Only OP can tell what's the case here. :)
0

Author Commented:
Rob - your last formula did the trick. Thanks to both of you!
0

Finance AnalystCommented:

Also good to be a member of EE to enter into discussions over interpretation of requirements and solutions.
0

Author 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.