I have a mocrosft worksheet full of letters with Y and N for yes and no. I need to convert them to numbers to use on a program so i can compare all the results. There are 13 columns with all Y's and N's. instead of changing them one by one is there an easier was to change all of them so i can get them to 1's for yes and 0's for no
I have micrsoft 2007..

Select the whole worksheet (Ctrl-A) or the area you need to modify. Do one change at a time.
Go to Edit> replace. Enter "y" in find and the number you want into replace. Hit "replace all" . A window will open telling you it has done this. You can see it on your sreadsheet. Hit close.
Repeat for "N".

Use the Edit >> Replace function. Highlight the area with what you want to change, bring up the replace dialog. (CTRL+H)

go to the edit menu, replace
Find what: Y
Replace with: 1
and clic on replace all
do the same for N and 0

Hi,

You can achieve your objective in a variety of ways, for example:

***first make backup of the original Microsoft Excel file, so in case anything goes wrong, you can always revert to actual file***

1) Use Find and Replace command:

Select the columns, and hold down CTRL key then press "H" key. In the resulting popup window, type "Y" in "Find what" and "1" in "Replace with".

You can replace all one by one or all in one go (using Replace ALL button).


2) Using "COUNTIF" worksheet function to count Ys and Ns in each column enter this formula (in same way as we put SUM() function)

=COUNTIF(B1:B100,"Y") >> (B1:B100 is assumed range, change it to actual) *** in row B101 ***

=COUNTIF(B1:B100,"N") >> (B1:B100 is assumed range, change it to actual) *** in row B102 ***


3) You can also use PIVOT table report to summarize your data.


For further help on "COUNTIF" and "PIVOT TABLE" reports, please refer to Microsoft Office's offline help, it contains all the detailed instructions!


Hope it helped!


Mohammad Taimur