Counting names within a cell

Hi,

I have many rows of data formatted in the following way within each cell.

43598: Joe Bloggs
38768: John Smith
97568:
87231: John Smith


I would like to count the number of times in each cell that a reference number appears but no name is present next to it (i.e. 1 in the above example). The result can appear in a seperate column next to the data.

I would assume this is something that could be done with counting the length between carriage returns and/or related to the colons after the reference numbers, unfortunately my excel skills have not come up with a solution and I have not found anything similar enough by searching.

If this is possible, I am not bothered whether I end up using a cell formula or VB code.

thanks in advance.

Are all reference numbers the same length? That is 5 characters as per your example?

If so, do you want to show a total in some sort of row beneath all the data column, or do you want excel to show a 1 right beside each entry?

Try this in the mean time (solution without VB).

Insert a column right beside your data, assuming your data is on A1:A100, insert a column after that and insert the following formula in B1=IF(LEN(A1)>=7,0,1) and drag up to B100
Then you place the sum of B1:B100 anywhere you want and then hide column B. Naturally you will not put the sum in column B, otherwise it'll be hidden.

Try:

Please Login or Register  to view this content.

Entered with Ctrl-Shift-Enter (CSE)

Cheers,

Originally Posted by tackit

Hi,

I have many rows of data formatted in the following way within each cell.

43598: Joe Bloggs
38768: John Smith
97568:
87231: John Smith


I would like to count the number of times in each cell that a reference number appears but no name is present next to it (i.e. 1 in the above example). The result can appear in a seperate column next to the data.

I would assume this is something that could be done with counting the length between carriage returns and/or related to the colons after the reference numbers, unfortunately my excel skills have not come up with a solution and I have not found anything similar enough by searching.

If this is possible, I am not bothered whether I end up using a cell formula or VB code.

thanks in advance.

are the reference numbers unique? ie, will 12345 be in more than 1 cell and if so do you want that counted as say 2 seperate instances of a number, or the total number of times for 12345 =2

I just had another thought, is the cell containing 20 sets of names and numbers got carriage returns? if you stretch say A1 with your example out longways, does the text still stack one on top of the other or is it turning into 1 long line of names and numbers?

I can't emphasise enough how useful a sample file would be at this stage...

If there is some consistency regards position of carriage return and last char on that line it may be that you can conduct some comparisons between position of colon and carriage return.

Seeing is believing etc...

Just to elaborate on my thinking - using the samples:

Please Login or Register  to view this content.

where A1 contains the string of interest - assumes a space always follows colon (even if "name" is empty)

In retrospect - further to prior suggestion the below alternative makes more sense:

Please Login or Register  to view this content.

similar premise as before