Change format of telephone numbers in a column

[bry333]

I would like to convert the format of telephone numbers that are in a column. Some of them are as "(213) 233-3434" or have the word "tel" in the column, such as "Tel: (213) 233-3434". I would like for all phone numbers to be shown as "213-233-3434". I am very new at this so explicit instructions would be most helpful. I love the thought of using VBA Excel and really look forward to learning it as quickly as possible.

I am also new to this forum so I promise that any blunders are genuine mistakes and if you point out my error, I will quickly apologize and try very hard not to make the same mistake again.

Thanks,
bry333

[spitfireblue]

Hi bry333,

Not always an easy one to do... assuming your data starts in A1, please see the formula below:

=TEXT(SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),"000-000-0000"

[bry333]

Hi, I googled my way through a start - I think. I created a module and edited it with your code. I ran it and it immediately highlighted the = sign at the beginning and gave me a dialog box from Microsoft Visual Basic for Applications, Compile error:
Expected line number or label or end of statement. I clicked Help but it took me to a page that did not help. So I clicked OK even though I didn't feel OK. :-)

[bry333]

Thanks Stephen!!! Wow, what a fast response. I am completely new at this. I have Excel 365 and I have turned on the Developer option. I assume I select Developer and then Visual Basic. But I do not see how to enter your code. It starts at column H2 by the way and goes to 1662. I am also not sure if there is other action required - in addition to starting the VBA - which as I have indicated, I do not know how to do. Is there other action that will be required once it starts?

I really wish I could hear your thoughts on how you created this. I realize that would take too much time. Do you perhaps have a youtube channel? Your answer would make for a great program. In lieu of knowing what that code means, if you could just tell me how to start it, I would be forever grateful. And thank you again!
bry333

[spitfireblue]

It's just a formula... you don't need a macro to do this.

[spitfireblue]

The basic idea is that you need to extract the numbers only from each cell and then format them. The format part is easy... its just

=TEXT(x,"000-000-0000")

where x is the number that you are formatting.

Getting the numbers from the cell without any other text, spaces, brackets etc. is the long part of the formula in the first solution or the function in the second solution.

[bry333]

After studying the spreadsheet, I realized I could just manually delete the text such as "Tel:" in each of those cells in Column H without too much trouble. So now, I only have to convert from "(213) 618-2345" to format "213-618-2345" in Column H row 2- row 1662. There is a column that for some reason uses the format as "Tel. (213) 618-2345" and in that column K row 2 - row 1662. There are so many instances of the text that it would be much better to use a formula to do the tedious repetition. In fact, that is the single greatest use I will have for this knowledge - to avoid tedious manual repetition. I will want to delete the "Tel. " and convert "(213) 618-2345" to format "213-618-2345".

It really doesn't matter to me whether I use a macro or just a formula but I need the steps for doing either. I am just starting and hence do not even know how to start a macro or a formula. I do appreciate the time you are spending. I know it must take a great deal of patience to deal with this level of ignorance. I am searching with google for help in addition to carefully reading your help. But so far, I have not had any success.

Thanks again for hanging with me.
bry

[spitfireblue]

If you want to use a macro you can create a new module and paste the following code:

Function onlyDigits(s As String) As String
    ' Variables needed (remember to use "option explicit").
    Dim retval As String    ' This is the return string.
    Dim i As Integer        ' Counter for character position.

    ' Initialise return string to empty
    retval = ""

    ' For every character in input string, copy digits to return string.
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next

    ' Then return the return string.
    onlyDigits = retval
End Function

(Credit to whoever wrote this code)

Then you can use the following formula...

=TEXT(onlyDigits(A1),"000-000-0000")

[bry333]

Stephen, thanks again, I started to try the macro but I do not see how to tell it to start with H2 and go to H1662.

So I thought I would try the formula. But I do not see how to start that either. I'm sorry but I did say I am new at this. I assume I highlight the H column, but from there, nothing is intuitive.

Stuck in a sinkhole of ignorance.

[spitfireblue]

Just use the formula... you just need to change A1 to whatever cell the first bit of data is in and then fill down.

[bry333]

I do not know what "fill down" means. And I wish someone would explain what I do to use the formula. I'm grateful to have it but in my research it seems that it is difficult to find out what must be the obvious for you - namely how to run the formula. I will continue to Google. I'm sure someone will eventually help by explaining the "obvious".
Thanks

[spitfireblue]

I suggest you use YouTube... or maybe take a Excel course.
Here is a clip of how to fill down (auto fill formula's): https://www.youtube.com/watch?v=7hAMzfupFjQ
If you can attach a sample sheet it would make it easier for us to help you.
From what I can tell your original data is in K2:K1662 so your formula in H2 should be:

=TEXT(SUMPRODUCT(MID(0&K2,LARGE(INDEX(ISNUMBER(--MID(K2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),"000-000-0000"

Then hover over the bottom right hand corner of H2 until your cursor turns into a black + and then select and drag down to H1662.

[bry333]

I almost have it. I realize I needed to fill down in a new column. I did the first cell and it suggested a correction which I accepted. (It seemed that it just left out the $ signs.) The first cell it created was correct. However, I am unable to fill down from that first cell. The following cells have #NUM! written in them. I tried clicking the enter key which I gather is how I make it do the formula but that doesn't work.

[bry333]

When I don't accept the correction, It says it is missing an opening or closing parenthesis. When I add a closing parenthesis, it seems happy with that. However, when I fill down, I can hit enter and step down through the cells, but the correction does not happen. So now I am stuck.

But I am happy to be able to run a formula and fill down.

By the way, when I modify the formula for H2 which is the column without text but just the wrong format, I only get the word "#VALUE!" in the column 2nd row cell. I do not get the correct phone number format as I did when I ran it for the K column.

Thanks!

[bry333]

Thanks, that helped. I am taking two courses on youtube "VBA is fun" and "Excel is fun". But I have spent a great deal of time without finding out exactly what I want to know.

I have telephone data in the wrong format in both H and K columns. In the H column, it is just the number. In the K column, all numbers have "Tel. " in front of the phone numbers which are also in the wrong format. From what you said, it seems that I cannot run the formula in the same column that has the data I want to correct. Is that right?

Thanks again!!!

[spitfireblue]

With the formula pointing to H2 with the #VALUE means that there is an error with the formula.

With the formula pointing to K2. You definitely need the final bracket (my bad - I guess it didn't copy properly when I tested it), but you also need the $ signs as well. The $ mean that the 1:25 does not become 2:26 when you fill down instead it stays as $1:$25 (look up how to use absolute values on YouTube). Having the final bracket and the $ signs means that your #NUM error should be fixed when you fill down.

[bry333]

I think my problem is that there are empty cells. These columns are for cell phone numbers and not everyone has a cell phone. It seems that as soon as there is an empty cell, it quits working.

Would that be right?

[bry333]

Strange, I just tried it again and the K column conversion works perfectly. In the cells that did not have numbers, it places 000-000-0000. Is there a formula for deleting those?

Pointing to H still gives me the VALUE comment. Is there a way to fix that?

Thanks so much. I am finally having fun with this thanks to you. You really deserve a huge THANKS!!!!

[spitfireblue]

Can you post what you have as an attachment so I can help you with the formula that is pointing to column H?

For the blank cells (pointing to column K) you can amend the formula to this:

=IF(K2="","",TEXT(SUMPRODUCT(MID(0&K2,LARGE(INDEX(ISNUMBER(--MID(K2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),"000-000-0000"))

[bry333]

I can't seem to copy paste part of a column into a sample to upload.* I do not know what I am doing wrong.* But column H looks just like you show except for many missing cells.* In other words, "(213) 345-3535" in one cell and then similar format down about 4 rows, etc through 1663 or so.

In addition, I have one last column I with some names and cell phone numbers.* Many of the cells have two names and the corresponding cell number.* For instance, in that column, a cell might have "John Cell (214) 345-3456* Abby Cell (214) 356-7865".* Once again, because not everyone has a cell phone, there might be empty cells.* The reason some cells have two cell phone numbers is that each row represents data about an email address. Sometimes, two people have a single email address and therefore, the row which corresponds to an email addresses for two people will have both people in Column I in the row cell for that single email address.*

When the couple have separate email addresses - more than half the time, - then the column L will have the husband (for instance) and then in the row below the wife (just for example).* The column I has each of their cell phone numbers with their name in front of the cell number associated with each row that has their separate email addresses so Column I will have in those cases - for instance:* "John Cell (353) 342-9878" in a cell above his wife Nancy with "Nancy Cell (345) 935-3425" in the row below John.

This is definitely more complicated to explain. I will try the new formula in some of the cells to see how it works and I will keep trying to copy paste and example.

Thanks much, I am almost done!!!

[bry333]

I used your latest formula on Column I and it almost worked. See snip attached. You can also see part of H.

[bry333]

That last formula fixed K. H is exactly has you show it in your sample EXCEPT that there are many empty cells. I tried to copy and paste to create a sample such as you did, but for some reason, I am unable to. That is very aggravating. I will keep trying even though it will not reveal anything other than I have said.

Thanks so much!