Excel VBA Tips n Tricks 47 Custom Function Phone Number Function to Add Spaces, Hyphens and Parent

With this function, you can automatically format Phone numbers.
From 5556677 to 555-6677
From 918.4996789 to [phone] etc. Check it out!

Fantastic Developer Tools:

[ExcelVbaIsFun]

Good points. 1. There are several ways to skin a cat.2. The second portion, I try to keep my variables open to be maneuvered/manipulated until the very end, so they're dynamic. You're absolutely right about the Phone = . . ., and that would work just fine. Thanks!

[beproductive]

Dan, I think I figured out why the substitute didn't work for the three periods. When you type 3 periods Auto-correct actually replaces the three periods with one character, an ellipsis. Therefore, in order to correct the problem you have to paste the ellipsis into the substitute function instead of three periods. Then your UDF works perfectly.

Thanks for all the videos!

[krn]

Very cool. I have stumbled around this with format( but never thought of cleaning it up prior or using & and left to join. Brilliant. Thanks Dan.

[beproductive]

Grahn, that makes more sense now. I looked a little closer at the code and your right. The variable appears correctly but it doesn't put it on the sheet with a leading 0. I believe that if you add the line of code: sheets(4).cells(x, 3).numberformat="@" to the beginning of your loop it will solve your problem. Sorry for not understanding things clearly before. Hope it works! :-)

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[ExcelVbaIsFun]

Thanks David! My apologies to all, my email has been filtering out pretty much everything the past 2 or 3 days, and now it just all appeared!

[beproductive]

Grahn, I don't know what to tell you. Using the VBA that I showed in my last post I get it to work every time. Did you try using the formatting that I showed? I believe that in order to keep the leading zero you have to format it as text. That is what the "@" symbol does. To be more explicit your code should be: Z=Format(Mid(Cells(x, 2), InStr(Cells(x, 2), "-") + 2, 5), "@")

[krn]

Found the ellipsis symbol. Hold down ALT and from number keypad type 0 1 3 3 and the "…" will appear. Just insert that into your substitute function and all works fine like David states.

[grahnc]

That's OK, I appreciate the effort, anything helps. Your suggestions helped me to familiarize the Format Method. I re-wrote the text the same way you are showing it and it still doesn't work. I really don't know why. I've been doing a lot of studying but I have a long way to go. Thanks for all you have done.

[krn]

Ps. Like the new channel icon. Did you make that yourself?

[krn]

Have you formatted the actual cell to accept the in Format Cell (CTRL 1) ? Highlight the range or column that the zip code is going into, press CTRL 1, go to Custom and type as the format. This will allow Excel to display anything entered into those cells as 5 places. For example 21 would appear as 00021. This should fix your issue.

[beproductive]

Wrap your code so that it is formatted as text. This will allow you to keep the leading zero. It will look like this: Z=Format(YOUR CODE, "@")

[grahnc]

Yep, I've tried a bunch and similar to your "..." issue, but not exact, I cant get my 0 to appear. It is only if the 0 appears as the first digit.

Lets say a MA zip such as 02189 will appear as 2189 but a NY zip such as 10004 will appear as 10004.

Again, I just started with VBA last week when I stumbled upon your videos. Since then I have had nothing else on my mind. I'm a beginner.

[ExcelVbaIsFun]

Yes, or at least format the cell as "Text".

[grahnc]

Sp sorry if this is becoming excessive but I need to point something else out.

When I run step up and complete a cell such as 01234, after hovering over z, it shows 01234
below the code, I have, Sheets(4).Cells(x, 3) = z. When the code places the value of z into cell x it is displayed as 1234 not 01234. That might shed some light, hopefully.

[chameleoncustoms]

Is it possible to implement this function to format a UserForm Textbox ?

[grahnc]

Hi, I am doing something similar. My data is in a range, with each cell containing something like this "Roslindale - 02131-1736 MA". I am pulling out the first five digits from the string. My problem is VBA is recognizing the 0 as nothing, returning the value as 2131 instead of 02131. Any suggestions would be great. For a look at the code I'm doing this "Z = Mid(Cells(x, 2), InStr(Cells(x, 2), "-") + 2, 5) "

[ModelbaanTBM]

At 8:20, why don't you just use the VBA Replace function? And I would also think it would be easier to replace the "MyNum = left(..." etc. by "Phone = left(..." etc.

[ModelbaanTBM]

PS: for WorksheetFunction, you don't really need to put 'Application' in front of it.

[grahnc]

Thanks David, I've tried format, Still not working. I've declared the variable as everything from long to variant and applied the format. I've even used, instead of "@", 0 and & and # and 0#### and Nothing seems to work.

Let me let you know that i am new at VBA, started watching ExcelVBAIsFun a week ago.