+ Reply to Thread
Results 1 to 22 of 22

Change format of telephone numbers in a column

  1. #1
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Change format of telephone numbers in a column

    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

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

    Hi bry333,

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  3. #3
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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
    Last edited by bry333; 08-30-2017 at 10:52 PM.

  4. #4
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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. :-)

  5. #5
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

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

  6. #6
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

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

    Please Login or Register  to view this content.
    (Credit to whoever wrote this code)

    Then you can use the following formula...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by spitfireblue; 08-30-2017 at 11:37 PM.

  7. #7
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

    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

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    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.

  8. #8
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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.

  9. #9
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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
    Last edited by bry333; 08-31-2017 at 01:14 PM. Reason: correction for clarity

  10. #10
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

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

  11. #11
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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

  12. #12
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

  13. #13
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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!!!

  14. #14
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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.

  15. #15
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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!
    Last edited by bry333; 08-31-2017 at 09:14 PM.

  16. #16
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

    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.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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?

  18. #18
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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!!!!
    Last edited by bry333; 09-01-2017 at 12:08 PM.

  19. #19
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Change format of telephone numbers in a column

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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!

  21. #21
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

    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!!!
    Last edited by bry333; 09-01-2017 at 09:14 PM.

  22. #22
    Registered User
    Join Date
    08-21-2017
    Location
    Georgia, USA
    MS-Off Ver
    2016
    Posts
    14

    Re: Change format of telephone numbers in a column

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Fixing Format for Telephone Numbers
    By chcalissie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-07-2015, 09:42 AM
  2. [SOLVED] I want telephone numbers and time of any format to specific format
    By Latha Mani in forum Excel General
    Replies: 16
    Last Post: 01-11-2014, 09:58 AM
  3. [SOLVED] Change Format of Telephone Numbers in Excel
    By mrluckydawg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-14-2013, 03:17 PM
  4. Custom format UK telephone numbers/UK post codes
    By Ju1cy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-13-2013, 07:00 AM
  5. Reformat column of telephone numbers
    By Maree in forum Excel General
    Replies: 2
    Last Post: 03-01-2006, 05:35 PM
  6. Replies: 2
    Last Post: 03-01-2006, 02:30 PM
  7. how do i format telephone numbers
    By Vin in forum Excel General
    Replies: 1
    Last Post: 05-29-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1