User Defined FUNCTION
 

I need a User Defined Function that check if a number or string is 8 digits
long and ends in "0000". If it meets the two creteria, then it should drop
those the "0000" and return only the remaining 4 numbers for example,

23000016 result 23000016
23160000 result 2316
19000000 result 1900

FYI using Substitute() gives me the wrong result sometimes.

FUNCTION Truc(location)

END Function

User Defined FUNCTION
 

IF(AND(LEN(A1)=8,RIGHT(A1,4)="0000"),LEFT(A1,4),A1 )

--
__________________________________
HTH

Bob

"EAK" wrote in message
...
I need a User Defined Function that check if a number or string is 8 digits
long and ends in "0000". If it meets the two creteria, then it should drop
those the "0000" and return only the remaining 4 numbers for example,

23000016 result 23000016
23160000 result 2316
19000000 result 1900

FYI using Substitute() gives me the wrong result sometimes.

FUNCTION Truc(location)

END Function

User Defined FUNCTION
 

As opposed to a udf how about this...

=IF(RIGHT(A1, 4) = "0000", A1/10000, A1)
--
HTH...

Jim Thomlinson


"EAK" wrote:

I need a User Defined Function that check if a number or string is 8 digits
long and ends in "0000". If it meets the two creteria, then it should drop
those the "0000" and return only the remaining 4 numbers for example,

23000016 result 23000016
23160000 result 2316
19000000 result 1900

FYI using Substitute() gives me the wrong result sometimes.

FUNCTION Truc(location)

END Function

User Defined FUNCTION
 

Give this UDF a try (assuming you wanted it named Truc as posted)...

Function Truc(S As String) As String
If S Like "????0000" Then
Truc= Left(S, 4)
Else
Truc= S
End If
End Function

Rick


"EAK" wrote in message
...
I need a User Defined Function that check if a number or string is 8 digits
long and ends in "0000". If it meets the two creteria, then it should drop
those the "0000" and return only the remaining 4 numbers for example,

23000016 result 23000016
23160000 result 2316
19000000 result 1900

FYI using Substitute() gives me the wrong result sometimes.

FUNCTION Truc(location)

END Function

User Defined FUNCTION
 

Why would you even use SUBSTITUTE?

=IF(AND(LEN(A1)=8,RIGHT(TRIM(A1),4)="0000"),TRUNC( A1,4)/10000,A1)


--


Regards,


Peo Sjoblom

"EAK" wrote in message
...
I need a User Defined Function that check if a number or string is 8 digits
long and ends in "0000". If it meets the two creteria, then it should drop
those the "0000" and return only the remaining 4 numbers for example,

23000016 result 23000016
23160000 result 2316
19000000 result 1900

FYI using Substitute() gives me the wrong result sometimes.

FUNCTION Truc(location)

END Function

User Defined FUNCTION
 

The OP mentioned he wanted this only if the number is 8 digits long as well.
True, his examples all show 8-digit numbers, but I made the same kind of
assumption in another thread yesterday and my assumption turned out to be
wrong. So, just in case...

=IF(AND(RIGHT(A1,4)="0000",LEN(A1)=8),A1/10000,A1)

Rick


"Jim Thomlinson" wrote in message
...
As opposed to a udf how about this...

=IF(RIGHT(A1, 4) = "0000", A1/10000, A1)
--
HTH...

Jim Thomlinson


"EAK" wrote:

I need a User Defined Function that check if a number or string is 8
digits
long and ends in "0000". If it meets the two creteria, then it should
drop
those the "0000" and return only the remaining 4 numbers for example,

23000016 result 23000016
23160000 result 2316
19000000 result 1900

FYI using Substitute() gives me the wrong result sometimes.

FUNCTION Truc(location)

END Function