Counting the number of spaces in a text string

[Data Cruncher]

I'm frequently dealing with historical lists of names that need to have the given names and the surname split into separate fields.

Sometimes the Surname is provided first, sometimes last and sometimes not at all - see below:-

Mary Jo Beth JONES
Mary SMITH
Peter William EVANS
Richard
SMITH John

The GivenNames frequently consist of 1, 2 or 3 words, and very occasionally 4-7 (such as "unnamed infant gender not known stillborn twin RAMSBOTTOM")

If I know the surname is first, I only have to run the FIND function to get the space's position, minus one, to do a LEFT function for the Surname data, and then a MID or RIGHT function to extract the GivenNames data.

If the Surname is last in the original field, I need to be able to find the last space in each original field.

Again, if all the fields had the same number of Given Names (say 3 given names and a Surname), I could run the following formula to identify the position of the last space in the column:-

=FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)
Mary Jo Beth JONES then returns 13

As many people are not recorded with more than one Given Name, and many are recorded with more than two, how do I create a formula to count the number of spaces in a field so I can then use that figure to determine how the formula is constructed?

Example:

A B C D E F G
1 Names LEN Formula #Spaces LastSpacePlace GivenNames Surname
2 Mary Jo Beth JONES =LEN(A2) ? 3 =FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1) =LEFT(A2,E2-1) =RIGHT(A2,B2-E2)
3 Mary SMITH =LEN(A3) ? 1 =FIND(" ",A3) =LEFT(A3,E3-1) =RIGHT(A3,B3-E3)
4 Peter William EVANS =LEN(A4) ? 2 =FIND(" ",A4,FIND(" ",A4)+1) =LEFT(A4,E4-1) =RIGHT(A4,B4-E4)
5 Richard =LEN(A5) ? 0 0 =LEFT(A5,B5)
6 SMITH John =LEN(A6) ? 1 =FIND(" ",A6) =RIGHT(A6,E6-D6-1) =LEFT(A6,E6-D6)

results in:-

Names LEN Space#Formula #Spaces LastSpacePlace GivenNames Surname
Mary Jo Beth JONES 18 ? 3 13 Mary Jo Beth JONES
Mary SMITH 10 ? 1 5 Mary SMITH
Peter William EVANS 19 ? 2 14 Peter William EVANS
Richard 7 ? 0 0 Richard
SMITH John 10 ? 1 6 John SMITH



Many thanks to those who take the time to look at my question and ponder how to solve it.

Data Cruncher.

[martindwilson]

=len(a1)-len(substitute(a1," ",""))

[NBVC]

Try

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

where A2 contains string.

[martindwilson]

then you could assuming if there are no spaces use text as it is then add1 and use that in choose
=choose(len(a1)-len(substitute(a1," ",""))+1,a1, formula 1,formula 2,formula 3,....)

[daddylonglegs]

When you show the surname in CAPS is that how the data actually looks or have you included that yourself?

[Data Cruncher]

Hello Daddylonglegs and the others who have so kindly provided the answer I so badly needed.

Regarding the surnames being in Capitals or not, the original data can be in either order and in any available case all in one field, based on the principle of GIGO.

I do the right thing by people (dead and alive) and the data I work with by always putting Surnames in capitals and in separate fields.

This is helpful when entering details on, or dealing with, people whose custom is to put the surname/family name first, such as is done in most Asian countries.

The practice is applicable for name tags, address labels and the like and for finding entries in the page of a book or article. In a good database, the Surname will always be recorded separately from the GivenNames, but most available family history databases are definitely not good.

I have, therefore, to work my through several thousand entries for each project, trying to determine if the father's name could be John THOMAS, John Thomas, Thomas John or Thomas JOHN, for example, in the case of the deaths of women of marriageable age.

This means I have to compare births with deaths with marriages and often also look for additional records such as burials, newspaper notices and so on. (In Australia there is next to no surviving Census data to be consulted.)

Lots of fun and very interesting, but rather time-consuming. Hence the need for a quick-fix.

Thanks to the Excel Help Forum, I can now do this; it will save me days of work per project. Yee-hah!!!

Cheers,

Data Cruncher