EXTRACT A CHARACTER FROM A CELL AND RETURN A NUMBER

[rwelly]

I have a problem! I need to report how many people are on our database. Our
database uses "&" to list each person. Therefore, in the event that cell a1 =
Mr & Mrs Smith
I need b1 to show 2

Mr & Mrs Smith & Master Smith
I need b1 to show 3

Mr Smith
I need b1 to equal 1 (or zero)

 

[Herb Tyson [MVP]]

What program are you using for your database?

Depending on what you're using, this isn't so much an extracting/returning
problem as a counting problem. What you want is the number of cells plus the
number of &s. Depending on what program you're using, this should be fairly
simple and straightforward.

 

[rwelly]

We use an access database but we need to upload certain data to a website in
a csv format. Therefore, we are creating a report in our database and then
running macros to put the report in the correct format (along with adding
other information not contained on our database). The problem i have
specifically relates to the number of &'s in each cell as i need to upload
the 'number of individuals' to the site.
Thanks for your help.
Rob