CODEWITHSUNDEEP
excelexcel-formulaudfvba
Nelson Valdivia
Nelson Valdivia

Reputation: 1

Trying to get Excel UDF custom cell formatting to return either nothing '(empty cell)' or 'formatted user input'

im trying to use a custom defined UDF function to format a huge long list of excel phone numbers I have, as well as any future phone number entries I add to this excel file.

Aside: the UDF itself just formats phone numbers, cleanly, so that no matter if I enter a phone with or without extension, it will format it to be more readable. I found it online and tinkered with it to suit my needs. Thats not my question really,

My question is how can I pre-empt a cell to take the value of my input and apply that as my cell-formatting without returning a #VALUE for all cells that do not have a value yet.

Ive tried something to the effect of:

=IF(ISBLANK, "", =CustomUDF(cellvalue))

but dont think this will do the job.

if you want to see my UDF, here it is!:

Function cleanPhoneNumber(thisNumber As String) As String
    ' this function aspires to clean any phone number format
    ' to standard format (+9999) 999-999-9999 or 999-999-9999
    ' works with almost all phone number formats stored in text

Dim retNumber As String

For i = 1 To Len(thisNumber)
    If Asc(Mid(thisNumber, i, 1)) >= Asc("0") And Asc(Mid(thisNumber, i, 1)) <= Asc("9") Then
        retNumber = retNumber + Mid(thisNumber, i, 1)
    End If
Next
If Len(retNumber) > 10 Then
    ' format for country code as well
    cleanPhoneNumber = Format(retNumber, "000-000-0000-0000")
Else
    cleanPhoneNumber = Format(retNumber, "000-000-0000")
End If
End Function

Upvotes: 0

Views: 122

Answers (3)

The Dude
The Dude

Reputation: 314

Why not just format the cells that do not have a number in them yet with a custom format 000-000-0000-0000

Upvotes: 0

jkpieterse
jkpieterse

Reputation: 3006

Or alternatively:

=IF(cellvalue="", "", =CustomUDF(cellvalue))

Upvotes: 0

Tom K.
Tom K.

Reputation: 1042

Just add the following code:

if thisNumber = "" Then cleanPhoneNumber = ""

Upvotes: 1

Related Questions