Converting Text to Numerical Data For Use in Table

[Susie_2]

Hi,
I'm trying to make a table comparing how many clients write books with those who host podcasts. One column has book titles, and each title is different (those who don't write books have a 0 entered). I'm planning to use a new column to convert each book title name to a value of 1 (I'd like to keep the book names intact within their original column). This also goes for the podcast column too. Basically, how do I create 2 new columns with the labels of book title and podcast name with 0 and 1 values instead of the actual book title and podcast name? I would be assigning a value of 1 to text from a book title cell and recording it in another column. The graph will show a count of # of books written and # of podcasts.

Book Title Podcast Name
Cats Rule 0
Dogs Are Great Dog Talk
0 In My Opinion
Caring For Birds 0
0 Vet Tech Live
Rodents As Pets 0
Great Pets 0

Hopefully this isn't too confusing!

Thank you!
Susie

 

[Kaper]

Honestly - it is confusing
But lets try to guess.
assuming your books and podcasts titles/names are in columns A and B and row 1 is for headers in cell C2 write:
formula:

=ISTEXT(A2)*1

copy cell C2 down and right, and in columns C and D you will have 1's for any text in resp columns A and B and 0's for 0's, other numbers, and empty cells too

The other option woiuld be to count all texts in single operation, without creating helper columns C and D. The formula to be inserted in any cell (but cells in column A, so for instance cell E2 would be a good place to test) would be:

=COUNTA(A:A)-COUNT(A,A)-1

-1 at the end is to deduct from the count header row (which is text too).

Hope it helps

 

[Susie_2]

Yes, that totally worked!! I actually used the other formula for a table county countries, so I got a 2 for 1. Thank you again for your time working on this problem.

~Susie

 

[Kaper]

You are welcome, and thanks for the feedback.