# The need to use MID

#### JCScoobyRS

##### Board Regular
I have a number, 0.0045, and I need to take off the first 0. I tried this with MID but I must had done it wrong as it didn't work. Any ideas? Thanks, Jeremy

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
On 2002-09-30 17:06, JCScoobyRS wrote:
I have a number, 0.0045, and I need to take off the first 0. I tried this with MID but I must had done it wrong as it didn't work. Any ideas? Thanks, Jeremy

What should be the result?

.0045

given that some messages are being lost, don;'t know if this has already been answered! but how about something of the form:

=MID(A1,FIND(".",A1),LEN(A1)-(FIND(".",A1,1))+1)

P.S. this is all BC (before coffee), so no promises about 'shortest method' etc...
This message was edited by PaddyD on 2002-09-30 17:12

On 2002-09-30 17:10, JCScoobyRS wrote:
.0045

=IF(LEFT(A1)+0,A1,SUBSTITUTE(A1,LEFT(A1),"",1))

whose result will be a text-formatted number.

=TEXT(A1,".0000")

should work as well.

On 2002-09-30 17:19, Aladin Akyurek wrote:
On 2002-09-30 17:10, JCScoobyRS wrote:
.0045

=IF(LEFT(A1)+0,A1,SUBSTITUTE(A1,LEFT(A1),"",1))

whose result will be a text-formatted number.

One question, why the SUBSTITUTE to replace the first occurrence of LEFT(A1), and not use RIGHT(A1,LEN(A1)-1) instead ? seems a bit strange to me... is it just a matter of style ?

On 2002-09-30 17:51, Juan Pablo G. wrote:
On 2002-09-30 17:19, Aladin Akyurek wrote:
On 2002-09-30 17:10, JCScoobyRS wrote:
.0045

=IF(LEFT(A1)+0,A1,SUBSTITUTE(A1,LEFT(A1),"",1))

whose result will be a text-formatted number.

One question, why the SUBSTITUTE to replace the first occurrence of LEFT(A1), and not use RIGHT(A1,LEN(A1)-1) instead ? seems a bit strange to me... is it just a matter of style ?

Juan,

I did too many substitutions lately... so it's maybe a recency effect... Both

=IF(LEFT(A1,2)<>"0.",A1,SUBSTITUTE(A1,LEFT(A1),"",1))

and

=IF(LEFT(A1,2)<>"0.",A1,RIGHT(A1,LEN(A1)-1))

would do.

The initial formula has, although good enough, a terse condition, hence expanded here...

BTW, why is SUBSTITUTE strange?

On 2002-10-01 02:34, Aladin Akyurek wrote:
Juan,

I did too many substitutions lately... so it's maybe a recency effect... Both

=IF(LEFT(A1,2)<>"0.",A1,SUBSTITUTE(A1,LEFT(A1),"",1))

and

=IF(LEFT(A1,2)<>"0.",A1,RIGHT(A1,LEN(A1)-1))

would do.

The initial formula has, although good enough, a terse condition, hence expanded here...

BTW, why is SUBSTITUTE strange?

Why strange ? well, I don't know seemed like an unusual path for replacing a (one) character string with "", that's it...

Replies
2
Views
204
Replies
9
Views
208
Replies
2
Views
407
Replies
6
Views
403
Replies
5
Views
158

1,217,500
Messages
6,137,008
Members
450,038
Latest member

### 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