Solved

# Simplifying Excel Formula

Posted on 2016-09-01
62 Views
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
Question by:Go-Bruins
• 5
• 5
• 2

LVL 32

Expert Comment

ID: 41780082
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

LVL 28

Expert Comment

ID: 41780096
Try this....

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

LVL 28

Expert Comment

ID: 41780102
Sorry Rob! Didn't refresh the page before posting.
0

LVL 28

Expert Comment

ID: 41780104
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

LVL 32

Expert Comment

ID: 41780116
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

LVL 32

Accepted Solution

Rob Henson earned 400 total points
ID: 41780133
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

LVL 28

Assisted Solution

Subodh Tiwari (Neeraj) earned 100 total points
ID: 41780137
Ah.. didn't pay that much attention to it. My bad.
Thanks for pointing this out.
0

LVL 32

Expert Comment

ID: 41780143
Maybe a literal translation mistake. Aardvark is indeed the first name in the cell and Hur is the last name in the cell.
0

LVL 28

Expert Comment

ID: 41780148
Only OP can tell what's the case here. :)
0

Author Closing Comment

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

LVL 32

Expert Comment

ID: 41780155

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

Author Comment

ID: 41780156
Just to clarify:

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

Thanks again.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.