User Defined FUNCTION

[EAK]

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

 

[Bob Phillips]

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

 

[Jim Thomlinson]

As opposed to a udf how about this...

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peo Sjoblom]

Why would you even use SUBSTITUTE?

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


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

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