Formula producing unexpected results

[johnmerlino]

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:

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

 

[Peter_SSs]

Any idea what's causing this?

Yes, your third formula is the main problem

=IF(LEN(B4)-LEN(SUBSTITUTE(B4," ",""))=2,MID(B4,LEN(C4)+2,FIND(" ",B4,LEN(C4)+2)-LEN(C4)-2),"")

It basically says: If column B is 3 words then return the red bit, otherwise return the blue bit. In your sample, the data has 4 words so this formula returns the blue bit which is a null string.

The problem in the next cell largely flows from this, but I'm not going in to detail there because you seem to be saying that you are going to get rid of the fourth word before you do any of this anyway.

we can ignore Defend for now, as I would take that out before hand

 

[hiker95]

johnmerlino,

If you wanted to use a User Defined Function.

Excel Workbook
	B	C	D	E	F	G
4	Carlos Alberto Nassimoff Defend	Carlos	Alberto	Nassimoff	Defend
5	Carlos Alberto Nassimoff Defend	Carlos	Alberto	Nassimoff	Defend
6
7	aaa 123 bbb 222	aaa	123	bbb	222
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Function
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Function GetTN(ByRef TNs As String, ByVal Position As Integer) As String
' Get 1st 2nd 3rd... word or number from a string of text and numbers separated by a space or spaces.
' Author:  Stanley D. Grom, Jr., stanleydgromjr at ExcelForum.com, hiker95 at MrExcel.com
' Modified: September 03, 2010
'
' A1 is equal to (without the " marks): "Aa 11 Bb 22 Cc 33Dd Ee44"
' B1: =GetTN(A1, 5)
' The result is "Cc"
'
'Modification from:
'Function GetClaim(ByVal Claims As Range, ByVal Position As Integer) As String
'Function GetClaim(ByVal Claims As String, ByVal Position As Integer) As String
'Author:  Leith Ross
'http://www.excelforum.com/excel-programming/744090-string-manipulation-split-into-multiple-variables.html
'
Dim Cnt As Integer
Dim Matches As Object
Dim RegExp As Object
Dim S As String, Text As String
Application.Volatile
Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Global = True
RegExp.IgnoreCase = True
RegExp.Pattern = "\s*(\S+)\s+(.*)"
Text = TNs & " "
Do While RegExp.Test(Text)
  S = RegExp.Replace(Text, "$1")
  Text = RegExp.Replace(Text, "$2")
  Cnt = Cnt + 1
  If Cnt = Position Then GetTN = S
Loop
End Function

 

[Peter_SSs]

I think it would be simpler and more efficient to use standard worksheet functions, with this formula copied across and down. I've only used 3 result columns because I think that is all the OP wants, however, it could easily be copied across a further column if desired.

hiker95: Also note that using COLUMN() instead of COLUMNS() would leave that form of your formula susceptible to error if any clolumns are inserted to the left of your formulas.

Excel Workbook
	B	C	D	E
4	Carlos Alberto Nassimoff Defend	Carlos	Alberto	Nassimoff
5	Carlos Alberto Nassimoff	Carlos	Alberto	Nassimoff
6	aaa 123 bbb 222	aaa	123	bbb
7	Tom T Jones	Tom	T	Jones
Separate Names

 

[johnmerlino]

Thanks for your replies. Your last formula seems exactly what I was looking for a first, middle, and last with no spaces in their own columns. It seems though what you posted on worked for the first name. I think it's missing the formulas for middle and last?

 

[Peter_SSs]

Thanks for your replies. Your last formula seems exactly what I was looking for a first, middle, and last with no spaces in their own columns. It seems though what you posted on worked for the first name. I think it's missing the formulas for middle and last?

Did you do this?

... this formula copied across and down.

 

[johnmerlino]

Thanks for replies. Eventually got it working.