Concatenate Issue

[spanspace]

OK so my issue is this. I need line 5 to look like line 2 for anyone that has 3 names. This hasn't been an issue in the past and my old formula worked like a champ for people with just first and last. Now I have one person that has 3 names and it doesn't work as intended. I need help.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	UNIQUE ID						ID	Name
2	2427 T, MATTHEW PAUL						2427	MATTHEW PAUL TESTER
3
4	1838 R, JOHN						1838	JOHN ROBERTS
5	2427 T, MATTHEW						2427	MATTHEW PAUL TESTER
Sheet1

Excel 2010

Cell Formulas
Range	Formula
A4	=IF(G4=""," ",CONCATENATE(LEFT(G4,4)," ",LEFT(TRIM(CLEAN(RIGHT(SUBSTITUTE(H4," ",REPT(" ",50)),50))),1),", ",TRIM(SUBSTITUTE(LEFT(H4,FIND(" ",H4)),CHAR(160),CHAR(32)))))
A5	=IF(G5=""," ",CONCATENATE(LEFT(G5,4)," ",LEFT(TRIM(CLEAN(RIGHT(SUBSTITUTE(H5," ",REPT(" ",50)),50))),1),", ",TRIM(SUBSTITUTE(LEFT(H5,FIND(" ",H5)),CHAR(160),CHAR(32)))))

 

[Greg Truby]

This is an old problem with parsing names. While thinking about the problem of "Matthew Paul Tester", Would your data possibly contain names like:

• "John Roberts, Jr."

- or -

• "Norman A. Smith, III"?

 

[seenfresh]

Greg has a valid point...

If you say your formula worked great in the past then I am assuming you don't have that particular issue...

Your looking for something that will check to see if the cell has two or three names, if two names you can use your current code, if three names your current code needs to be adjusted...

IF(FIND("",H5)=FIND("",H5,FIND("",H5)+1),"Two Names","Three Names")

This statement will determine which formula to use... haven't looked at your formula in detail yet...

Just a thought!

 

[spanspace]

There is literally no special characters to look for in that name field. Its either the usuall First Last or in the one persons case First Middle Last.

Seenfresh I do have something temporary in place based on that one persons ID. Which is below. But I need a long term solution. I find the formula below messy and not ideal as a long term solution.

=IF(LEFT(G41,4)="2427","2427 T, MATTHEW PAUL",IF(G41=""," ",CONCATENATE(LEFT(G41,4)," ",LEFT(TRIM(CLEAN(RIGHT(SUBSTITUTE(H41," ",REPT(" ",50)),50))),1),", ",TRIM(SUBSTITUTE(LEFT(H41,FIND(" ",H41)),CHAR(160),CHAR(32))))))

 

[seenfresh]

How about the following:

=IF(G3="","",IF(ISERROR(FIND(" ",H3,FIND(" ",H3)+1)),G3&" "&MID(H3,FIND(" ",H3)+1,1)&", "&LEFT(H3,FIND(" ",H3)),G3&" "&MID(H3,FIND(" ",H3,FIND(" ",H3)+1)+1,1)&", "&LEFT(H3,FIND(" ",H3))))

Note, only accounts for two or three names...

 

[seenfresh]

Had to make a quick adjustment to account for the middle name... see below:

=IF(C3="","",IF(ISERROR(FIND(" ",D3,FIND(" ",D3)+1)),C3&" "&MID(D3,FIND(" ",D3)+1,1)&", "&LEFT(D3,FIND(" ",D3)),C3&" "&MID(D3,FIND(" ",D3,FIND(" ",D3)+1)+1,1)&", "&LEFT(D3,FIND(" ",D3))&" "&MID(D3,FIND(" ",D3)+1,FIND(" ",D3,FIND(" ",D3)+1)-FIND(" ",D3))))

Also may need to add a few Trim functions to eliminate leading or trailing spaces...

 

[spanspace]

Thanks worked nicely. I had to make one minor change. As the cell that G lands on comes out funky from the export.

=IF(LEFT(G9,4)="","",IF(ISERROR(FIND(" ",H9,FIND(" ",H9)+1)),LEFT(G9,4)&" "&MID(H9,FIND(" ",H9)+1,1)&", "&LEFT(H9,FIND(" ",H9)),LEFT(G9,4)&" "&MID(H9,FIND(" ",H9,FIND(" ",H9)+1)+1,1)&", "&LEFT(H9,FIND(" ",H9))&" "&MID(H9,FIND(" ",H9)+1,FIND(" ",H9,FIND(" ",H9)+1)-FIND(" ",H9))))

 

[spanspace]

See final revision. I noticed a trailing space that needed to be cleaned.

=IF(LEFT(G9,4)="","",TRIM(SUBSTITUTE(IF(ISERROR(FIND(" ",H9,FIND(" ",H9)+1)),LEFT(G9,4)&" "&MID(H9,FIND(" ",H9)+1,1)&", "&LEFT(H9,FIND(" ",H9)),LEFT(G9,4)&" "&MID(H9,FIND(" ",H9,FIND(" ",H9)+1)+1,1)&", "&LEFT(H9,FIND(" ",H9))&" "&MID(H9,FIND(" ",H9)+1,FIND(" ",H9,FIND(" ",H9)+1)-FIND(" ",H9))),CHAR(160),CHAR(32))))

 

[seenfresh]

Not sure why you need the Left function to check if G9 is blank, just an extra function not necessary.

Also, when I mentioned trailing spaces I meant trailing spaces within the actual data source not the results...but you may want to watch out for both.

TRIM(FIND(" ",H9))

is different than

FIND(" ",TRIM(H9))

in cases where the data source in H9 has trailing spaces...

 

[spanspace]

When I export the data from our internal database it does not export that field as a number. Just the easiest way to tackle that issue. For that particular piece I did not, but I did a find and replace for G9 It's the end of the day and I am feeling lazy.