johnmerlino
Board Regular
- Joined
- Sep 21, 2010
- Messages
- 94
Hey all, I have a formula producing unexpected results. I have a first name, last name, and middle initial in one column:
Carlos Alberto Nassimoff Defend
The idea is to get Carlos, Albert, and Nassimoff all in their own columns (we can ignore Defend for now, as I would take that out before hand).
This is for formulas I using starting in the B column:
The above produces this undesired effect:
column | column | column
Carlos | | berto Nassimoff Defend
It was supposed to be:
column | column | column
Carlos | Alberto | Nassimoff Defend
Any idea what's causing this? Thanks for response.
Carlos Alberto Nassimoff Defend
The idea is to get Carlos, Albert, and Nassimoff all in their own columns (we can ignore Defend for now, as I would take that out before hand).
This is for formulas I using starting in the B column:
Code:
=TRIM(A4)
=LEFT(B4, FIND(" ",B4)-1)
=IF(LEN(B4)-LEN(SUBSTITUTE(B4," ",""))=2,MID(B4,LEN(C4)+2,FIND(" ",B4,LEN(C4)+2)-LEN(C4)-2),"")
=MID(B4,1+LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+LEN(C4)+LEN(D4),30)
The above produces this undesired effect:
column | column | column
Carlos | | berto Nassimoff Defend
It was supposed to be:
column | column | column
Carlos | Alberto | Nassimoff Defend
Any idea what's causing this? Thanks for response.