Formula results a a number formatted as text

[Russk68]

Hi all,

I am using this formula to return a number that's left of the "x". This formula would return a 2 but formatted as text. How can I get the same result formatted as a number?


=IFERROR(LEFT(N41,SEARCH("x",N41)-1),"")

Thank you!

Russ

 

[Rick Rothstein]

Hi all,

I am using this formula to return a number that's left of the "x". This formula would return a 2 but formatted as text. How can I get the same result formatted as a number?

=IFERROR(0+LEFT(N41,SEARCH("x",N41)-1),"")

If you involve a text number in a mathematical operation, Excel will convert it to a real number in order to complete that mathematical operation. One of the standard methods to do this is to add 0 to the text number (see above in red) as doing that would not change its value.

 

[footoo]

=IFERROR(--LEFT(N41,SEARCH("x",N41)-1),"")

 

[Russk68]

Thank you Rick for the solution and the explanation.

 

[Russk68]

Than you footoo. This works as well. Can you explain what the -- does?

 

[Rick Rothstein]

Than you footoo. This works as well. Can you explain what the -- does?

It is a double negative which is equivalent to multiplying by minus one twice. Since minus one times minus one equals plus 1, putting -- in front of the number is equivalent to multiply that number by plus one. It is one of the other standard methods that I mentioned in my first response of involving a text number in a mathematical operation without changing the underlying value of the text number (adding 0 or multiplying by +1 does not change a number's value).

 

[Russk68]

Got it! Thank you very much for the lesson Rick!