Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula question | Excel Worksheet Functions | |||
formula question | Excel Worksheet Functions | |||
"IF" formula question | Excel Worksheet Functions | |||
time formula question... | Excel Discussion (Misc queries) | |||
SUM array formula question | Excel Worksheet Functions |