Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
User Defined Function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
User defined function | New Users to Excel |