The need to use MID

[JCScoobyRS]

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

 

[Aladin Akyurek]

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?

 

[JCScoobyRS]

.0045

 

[PaddyD]

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)

paddy

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

 

[Aladin Akyurek]

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.

 

[Juan Pablo González]

=TEXT(A1,".0000")

should work as well.

 

[Juan Pablo González]

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.

Aladin,

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 ?

 

[PaddyD]

just fyi on the different results from the suggestions offered:

Book1.xls
	A	B	C	D
1	Source	PD	AK	JP
2	22	#VALUE!	22	22.0000
3	0.045	.045	.045	.0450
4	1.123	.123	1.123	1.1230
5
6
Sheet1

Paddy

 

[Aladin Akyurek]

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.

Aladin,

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?

 

[Juan Pablo González]

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