How to create a UDF from a formula

[Youngdand]

HI,

I have a quite large formula i use on a regular basis to reformat some data that comes out of our sql. i would like to create a udf from it in order to shorten the formula.

The formula is as follows:

=IF(SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))=0,"Invalid Postcode",IF(SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))>3,"invalid postcode",IF(LEN(SUBSTITUTE(A2," ",""))<5,"Invalid Postcode",IF(LEN(SUBSTITUTE(A2," ",""))>8,"Invalid Postcode",TRIM(CONCATENATE(UPPER(LEFT(SUBSTITUTE(A2," ",""),LEN(SUBSTITUTE(A2," ",""))-3))," ",UPPER(RIGHT(SUBSTITUTE(A2," ",""),3))))))))

I'm not a complete novice with VBA, but would just like a little guidance on how to go about creating UDF's.

Thanks,

Dan.

 

[Special-K99]

This doesn't solve your problem but your post has highlighted a possible error in your formula.

The first two conditions of your formula are counting the number of digits 0-9 that exist in A2.
If this is 0 or greater than 3 then the postcode is invalid.

The third condition is removing all spaces from A2 and is the length of this is < 5 or > 8 then again the postcode is invalid.

However, here's a legitimate postcode "W1A 4WW" If that gets typed as "+1A WW" or "W1A 4$£" your formula doesn't give an invalid password error!!

There's probably a UDF already written for checking legitimate postcodes that checks alphabetic characters as well.

Thinking...

 

[Special-K99]

Havent checked these out but here are two UDF solutions

https://datanology.co.uk/2015/08/28/excel-udf-to-validate-uk-postcode/

https://www.mrexcel.com/forum/excel-questions/436118-uk-postal-code-validation.html

 

[mikerickson]

Have you considered using a named Formula instead.

Let me use a simpler example of the formula =A2+5

If you select B2 and define the name
Name: myFormula
RefersTo: =Sheet1!A2+5

and then put the =myFormula in B2 you should get the expected result.
Since, names follow the same absolute/relative referencing, putting =myFormula in B3 should return 5 more than A3 and =myFormula in D2 should return 5 five more than C2.

If you add $ to the definition, it will act accordingly.


This uses fewer resources than a UDF.

 

[Youngdand]

Hi,

Thanks for all your help. I did end up going with a another UDF posted on this forum.

https://www.mrexcel.com/forum/excel-questions/475161-uk-post-code-re-format.html


Regards,

Dan,