Excel, replace an "X" with a "1"
fifou69
Posted messages
88
Status
Member
-
fifou69 Posted messages 88 Status Member -
fifou69 Posted messages 88 Status Member -
Hello,
You may say, your title strangely resembles a bunch of questions entitled "Convert letters to numbers or vice versa."
That's true! But on my side, my problem remains... a problem!
So, on one side I have 5 columns with heating choices (electricity, natural gas, fuel oil, etc.). On the other hand, I have 2500 rows with apartments... However, the person who gave me this Excel file marked their choices with "X" and that bothers me when calculating the totals for each (How many apartments use natural gas, for example)!
I can’t add up "X" marks, so I would like to replace them with "1". But doing this on 2500 rows would require courage worthy of Aragorn...
Is this possible? If so, can someone help me?
Thank you for your help!
Florian
Configuration: Windows XP / Internet Explorer 7.0
You may say, your title strangely resembles a bunch of questions entitled "Convert letters to numbers or vice versa."
That's true! But on my side, my problem remains... a problem!
So, on one side I have 5 columns with heating choices (electricity, natural gas, fuel oil, etc.). On the other hand, I have 2500 rows with apartments... However, the person who gave me this Excel file marked their choices with "X" and that bothers me when calculating the totals for each (How many apartments use natural gas, for example)!
I can’t add up "X" marks, so I would like to replace them with "1". But doing this on 2500 rows would require courage worthy of Aragorn...
Is this possible? If so, can someone help me?
Thank you for your help!
Florian
Configuration: Windows XP / Internet Explorer 7.0
7 answers
Hello,
It is possible to change the X to 1, but it is also possible to keep the file as it is and count the X's
example in the range from A1 to A300 with this formula
=COUNTIF(A1:A300, "x")
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
It is possible to change the X to 1, but it is also possible to keep the file as it is and count the X's
example in the range from A1 to A300 with this formula
=COUNTIF(A1:A300, "x")
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Good evening,
1) replace "X" with 1:
Edition -> replace -> X in 'search' and 1 in 'replace'....
'replace all'
2) count X in a column B (for example)
=COUNTIF(B:B,"X") or: =COUNTIF(B2:B2500,"X") ....
--
If you need anything, just ask me.
1) replace "X" with 1:
Edition -> replace -> X in 'search' and 1 in 'replace'....
'replace all'
2) count X in a column B (for example)
=COUNTIF(B:B,"X") or: =COUNTIF(B2:B2500,"X") ....
--
If you need anything, just ask me.
Good evening,
I think that with the function =COUNTIF(range, "X") you have what you're looking for.
This will give you the number of "x" or "X" that are found in the specified range of cells.
I think that with the function =COUNTIF(range, "X") you have what you're looking for.
This will give you the number of "x" or "X" that are found in the specified range of cells.
Hello
You can sum the number of X with SUMPRODUCT.
You can put 1 instead of X using the "replace" function (ctrl + h) after selecting your range, and just check "entire cell" and click "replace all".
--
Always zen
You can sum the number of X with SUMPRODUCT.
You can put 1 instead of X using the "replace" function (ctrl + h) after selecting your range, and just check "entire cell" and click "replace all".
--
Always zen
Good evening everyone!
Well fifou you can say thank you... :-D
3 replies in 2 minutes...
--
If you need anything, just ask me.
Well fifou you can say thank you... :-D
3 replies in 2 minutes...
--
If you need anything, just ask me.