len user defined function
Posted by Richard on November 23, 2001 12:59 AM
Hi
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)
