len user defined function

Posted by Richard on November 23, 2001 12:59 AM

I am trying to write a userdefined function (if that is the right way to go about it) that will concatenate a zero to a cell if the cell is 11 characters long.

The formula i use at the moment (then pastespecial value) is:

=IF(LEN(G2)=11,CONCATENATE(0,G2),G2)

where G2 is the original string

thanks

Posted by Bob Umlas on November 23, 2001 7:30 AM

You're writing it as a worksheet formula, not a UDF. I don't think a UDF is necessary, but you can certainly do it:
Function AddZero(Rg as range) as String
If Len(Rg.Value)=11 Then
AddZero="0" & Rg.Value
Else
AddZero=Rg.Value
End If
End Function.
Then, in your worksheet, you can code
=AddZero(G2)