Complicated formula to dissect a name into multiple columns

[mrzackmullins]

Hello,

I've found a few formulae to do close to what I'm looking to do. Short story: I have a report that pastes into an excel "tool" that then digests information and merges it into a word form via mail merge. It generates as many as 1000 forms per day, so it's important to get things correct on the first try.

The report generates names in this format LASTNAME, FIRSTNAME MI where a name looks like this Smith, John H

I have a cell that captures the last name perfectly as it stops at the comma, but the other cell manages to capture the First and MI as it backtracks to the comma (but I don't want the MI attached to the first name).

The formula that I'm looking to somehow modify to eliminate the MI is as follows.

=RIGHT(A1,LEN(A1)-FIND(",",A1,1))

I'm a bit new to the LEFT/RIGHT and TRIM functions as I don't regularly have a need to use them, but the Word Document needs to isolate just the first name so this is unfortunately necessary. Any suggestions?

 

[WassimN]

Hello mrzachmullins

The cell entry sample you gave is delimited by a space, so you may want to look into Text-To-Columns to facilitate this. Text-To-Columns will look at the cell entry and divide it into Columns whenever it sees a dilimiter.

Also, if you still wnat to use a formula, then:
This will get you the last name: =CLEAN(TRIM(LEFT(A1,FIND(",",A1,1)-1)))
This will get you the first name: =CLEAN(TRIM(MID(A1,FIND(", ",A1,1)+1,E5-FIND(", ",A1,1))))
This will get you the MI: =CLEAN(TRIM(MID(A1,E5,FIND(" ",A1,E5))))

I did these from my head but you get the concepts.

Hope this helps

 

[ranman256]

try this custom, usage:
=getFirstName(A2)

Function getFirstName(ByVal pvWord)
Dim vFirst, vLast
Dim i As Integer, j As Integer


i = InStr(pvWord, ",")
'vLast = Left(pvWord, i - 1)
j = InStrRev(pvWord, " ") - 1
vFirst = Mid(pvWord, i + 1, j - i)
getFirstName = Trim(vFirst)
End Function

 

[mrzackmullins]

Hello mrzachmullins

This will get you the last name: =CLEAN(TRIM(LEFT(A1,FIND(",",A1,1)-1)))
This will get you the first name: =CLEAN(TRIM(MID(A1,FIND(", ",A1,1)+1,E5-FIND(", ",A1,1))))
This will get you the MI: =CLEAN(TRIM(MID(A1,E5,FIND(" ",A1,E5))))

I did these from my head but you get the concepts.

Hope this helps

I'm not sure what you're referencing with E5 so I can't attempt these formula options. If you explain what this is referring to I can amend the formula to see if it will work.


Thank you for your help!

 

[mrzackmullins]

I should also say that I'm trying to limit VBA usage in this document for multiple reasons, but thank you for the assist ranman256

 

[jtakw]

Hi,

Try these, works for names With or Without middle name or initial:

Book1
	A	B	C	D
1	Smith, John H		Smith	John
2	Smith, John		Smith	John
3	Smith, John Jay		Smith	John
Sheet6

Cell Formulas
Range	Formula
C1	=LEFT(A1,FIND(",",A1)-1)
D1	=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+1,255)," ",REPT(" ",50)),100))

 

[mrzackmullins]

Fantastic! Thank jtakw, that worked perfectly!

 

[jtakw]

You're welcome, welcome to the forum.