Total Length of extracted names

[LauraForiero]

	What function should I use to find the total length of the 3 extracted names (last + first & middle title?) I need one single expression for the entire column. Thank in advance
	A	B	C	D	E
	Full Name (Last, First & Middle plusTitle - the latter may be separated by either an ! or $ symbol).	Title (extracted)	First and the middle name if the latter exists. (extracted)	Last Name (extracted)	The total length of the 3 extracted names (last + first & middle + title) e.g. 25
6	Brouillet,Amy Anne!Miss	Miss	Amy Anne	Brouillet
7	Dupuis,Jean Paul$Monsieur	Monsieur	Jean Paul	Dupuis
8	Ethier,Guy!Mr	Mr	Guy	Ethier
9	Fu,Guo Qing$Prince	Prince	Guo Qing	Fu
10	Gowan,Louise Marie$Mademoiselle	Mademoiselle	Louise Marie	Gowan
11	McGill,James Patrick!Dr	Dr	James Patrick	McGill
12	Robinson,Jacques$Monsieur	Monsieur	Jacques	Robinson
13	Vinet,Jean Pierre!Mr	Mr	Jean Pierre	Vinet
14	Voigt,Claire Antoinette$Madame	Madame	Claire Antoinette	Voigt
15	Wong,Jia$Reverend	Reverend	Jia	Wong

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

 

[jscranton]

Do want just straight length or formatted with spaces?

Straight length is just =LEN(B6) + LEN(C6) + LEN(D6)

 

[LauraForiero]

This formula doesn't work, it always gives me an extra letter. For example Miss Amy Anne Brouillet is 20, the formula gives me 21, and so on

Do want just straight length or formatted with spaces?

Straight length is just =LEN(B6) + LEN(C6) + LEN(D6)

 

[XOR LX]

Hi,

=SUMPRODUCT(LEN(SUBSTITUTE(B6:D6," ","")))

Regards

 

[jscranton]

The issue is that the extracted values aren't trimmed. The formula above will work or use:

=LEN(TRIM(B6) + LEN(TRIM(C6) + LEN(TRIM(D6)

 

[apo]

Another one..

Private Sub CommandButton1_Click()
Dim i As Long
With Cells(1).CurrentRegion
For i = 1 To .Rows.Count
.Cells(i, 5).Value = Len(Replace(Join(Array(Cells(i, 2), Cells(i, 3), Cells(i, 4))), " ", ""))
Next i
End With
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:234px;" /><col style="width:97px;" /><col style="width:115px;" /><col style="width:65px;" /><col style="width:21px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Brouillet,Amy Anne!Miss</td><td >Miss</td><td >Amy Anne</td><td >Brouillet</td><td style="text-align:right; ">20</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Dupuis,Jean Paul$Monsieur</td><td >Monsieur</td><td >Jean Paul</td><td >Dupuis</td><td style="text-align:right; ">22</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Ethier,Guy!Mr</td><td >Mr</td><td >Guy</td><td >Ethier</td><td style="text-align:right; ">11</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Fu,Guo Qing$Prince</td><td >Prince</td><td >Guo Qing</td><td >Fu</td><td style="text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Gowan,Louise Marie$Mademoiselle</td><td >Mademoiselle</td><td >Louise Marie</td><td >Gowan</td><td style="text-align:right; ">28</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >McGill,James Patrick!Dr</td><td >Dr</td><td >James Patrick</td><td >McGill</td><td style="text-align:right; ">20</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Robinson,Jacques$Monsieur</td><td >Monsieur</td><td >Jacques</td><td >Robinson</td><td style="text-align:right; ">23</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Vinet,Jean Pierre!Mr</td><td >Mr</td><td >Jean Pierre</td><td >Vinet</td><td style="text-align:right; ">17</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Voigt,Claire Antoinette$Madame</td><td >Madame</td><td >Claire Antoinette</td><td >Voigt</td><td style="text-align:right; ">27</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Wong,Jia$Reverend</td><td >Reverend</td><td >Jia</td><td >Wong</td><td style="text-align:right; ">15</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[jscranton]

Not to over complicate things, but the answer all depends on whether the OP wants the length of DrJamesPatrickMcGill or DrJames PatrickMcGill or Dr. James Patrick McGill. Everyone is giving her the first (as in no spaces). I was taking her to mean she wanted the extracted middle "James Patrick" to include the space.

In any case, it seems like she found what she wanted so thread = solved.