# Converting Text to Numerical Data For Use in Table

#### Susie_2

##### New Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Kaper

##### Board Regular
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:
Code:
``=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:
Code:
``=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

##### New Member
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

##### Board Regular
You are welcome, and thanks for the feedback.

Replies
7
Views
486
Replies
4
Views
405
Replies
0
Views
149
Replies
3
Views
604
Replies
1
Views
578

1,136,341
Messages
5,675,206
Members
419,553
Latest member
hanahass

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back