# Formula results a a number formatted as text

#### Russk68

##### Well-known Member
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

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

Thank you Rick for the solution and the explanation.

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

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).

Last edited:
Got it! Thank you very much for the lesson Rick!

Replies
6
Views
228
Replies
14
Views
523
Replies
18
Views
2K
Replies
1
Views
529
Replies
2
Views
97

1,206,808
Messages
6,074,990
Members
446,112
Latest member
nmz1133

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back