Custom Function

Posted by Jack in Uk on January 09, 2002 4:34 AM

Hi guys i need to transfer data from 90.... to 11 and 13 digits, would like custon fubction called Drop9 but i need the 0 to start, and return the rest of the data all numbers, but when i hit over 10 digits it fails i have 11 and 13 digits, but starts as 12 and 14, simple enough to chop the lead 9 but then to add a zero???

Over ten digits on return fails..

Any ideas guys
Rdgs
Jack in Uk

Posted by Aladin Akyurek on January 09, 2002 5:18 AM

Hi Jack,

If a formula would be OK, I'd be interested in a small sample, along with expected results.

Regards,

Aladin

==========

Posted by Jack in Uk on January 09, 2002 1:16 PM

Hi Aladin-- i did post earlier but ??? 2nd try
say paris tel number is 13 digits and 14th is start 9 i ned to drop only the 9 and return the full 13 ie 0333112345678 as is so im having fun

same for UK they are 11 but my data is 12 ie 902701234567 so over 10 and it fails???
tried a few methods, or i cant get the leading zero

HTH
Jack

Posted by Aladin Akyurek on January 11, 2002 6:13 AM

Jack --

Is it

=IF(LEFT(A1)+0=9,REPLACE(A1,1,1,0),A1)

that you're looking for?

If not, please give a sample like

9123098543 0123098543
0453787691 0453787691

Aladin

======

Posted by Jack in UK on January 14, 2002 10:46 AM

Hi Aladin,
Well COOL FORMULA!

Works perfect i made "" not " "
so is P E R F E C T !

Chreers mate, i understand you moves so i have learnt a lot, i will try to convert to VBA Custom Function, just for fun

Take care friend
Jack in UK