Question about using the right or left formula
 

How do you get excel to recognize the data as a number when using the right
or left formula? For example, cell L73 has the following formula:

=RIGHT(D3,5)
The data this equation produces is +137. I have another cell that has a
formula that says =IF(ISNUMBER(L73),L73,"NO"). The answer I get is NO. I
should get +173. I have formatted cell L73 to be a number.

How do I fix this formula? Thanks

Being a text function, the right function will return text.
To cause Excel to perform an implicit type change from text to numeric try
one of these:
=--RIGHT(D3,5)
=RIGHT(D3,5)*1


Does that help?
--
Regards,
Ron


"Jambruins" wrote:

How do you get excel to recognize the data as a number when using the right
or left formula? For example, cell L73 has the following formula:

=RIGHT(D3,5)
The data this equation produces is +137. I have another cell that has a
formula that says =IF(ISNUMBER(L73),L73,"NO"). The answer I get is NO. I
should get +173. I have formatted cell L73 to be a number.

How do I fix this formula? Thanks

You can convert the text string +173 into a numeric value like this:
=VALUE(RIGHT(D3,5))

When I did this, tho, the number 173 appeared without the plus sign.
Is that critical? If it is, this custom format
+#,##0_);-#,##0
will show a pos or neg sign.

the --RIGHT(D3,5) works perfect. Thanks

"Ron Coderre" wrote:

Being a text function, the right function will return text.
To cause Excel to perform an implicit type change from text to numeric try
one of these:
=--RIGHT(D3,5)
=RIGHT(D3,5)*1


Does that help?
--
Regards,
Ron


"Jambruins" wrote:

How do you get excel to recognize the data as a number when using the right
or left formula? For example, cell L73 has the following formula:

=RIGHT(D3,5)
The data this equation produces is +137. I have another cell that has a
formula that says =IF(ISNUMBER(L73),L73,"NO"). The answer I get is NO. I
should get +173. I have formatted cell L73 to be a number.

How do I fix this formula? Thanks

=IF(ISNUMBER(VALUE(L73)),VALUE(L73),"NO")

This should also work. If you want the output to include "+", you will have
to change the format.

"Jambruins" wrote:

How do you get excel to recognize the data as a number when using the right
or left formula? For example, cell L73 has the following formula:

=RIGHT(D3,5)
The data this equation produces is +137. I have another cell that has a
formula that says =IF(ISNUMBER(L73),L73,"NO"). The answer I get is NO. I
should get +173. I have formatted cell L73 to be a number.

How do I fix this formula? Thanks