+ Reply to Thread
Results 1 to 16 of 16

Numbers will not format despite using text to columns tool

  1. #1
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Numbers will not format despite using text to columns tool

    Hi everyone! I am really stumped here. I have a range of cells I am trying to format in phone # format. However, even after using the text to columns tool to ensure it isn't text and is in number format first, I have had no luck. I click on number > special > phone number in format cells menu. It even shows the number formatted correctly there. When I push OK nothing happens. Sheet and workbook is not locked. No idea!

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Numbers will not format despite using text to columns tool

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: Numbers will not format despite using text to columns tool

    Please see attached sample data. You can see that although the numbers look correct, when selecting any cell that is in bold, the formatting does not exist. This is an issue because I am using a VLookup which requires the formatting to be standardized. Please help.
    Attached Files Attached Files

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Numbers will not format despite using text to columns tool

    Can you give an example of what you are trying to do? Just providing a list of phone numbers which all appear to be formatted correctly does not provide me with any information as to what you are trying to accomplish. From what you have provided, I see not issues. Help us to help you by giving specific examples of what is wrong and what the expected results should be.

  5. #5
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: Numbers will not format despite using text to columns tool

    No problem. So I would like the cells formatted in bold to have the format (###) ###-#### when selected. It looks fine from afar but when you select any bolded cell, the numbers in the formula bar shows up as ########## not (###) ###-#### as desired. This is not true for unbolded cells which have the same formatting in the formula bar as well as the cell. Please let me know if I have been clearer or not.

  6. #6
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Numbers will not format despite using text to columns tool

    Just use the format painter on the cell format that you want to use and then apply to all the other cells

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Numbers will not format despite using text to columns tool

    I'm not sure how you arrived at the attached workbook, but as it is at the moment your bolded items are numbers with phone number formatting. The remaining items are just plain text. You can see this if you either (a) put =isnumber(a2) in B2 and copy down or (b) remove the specific alignment in col-A and you will see that your bolded items are right aligned (the default for numbers) and your non-bolded items are left aligned (the default for text).

  8. #8
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Numbers will not format despite using text to columns tool

    I believe that it has nothing to do with formatting. It was how the data was originally entered into the spreadsheet. If it was entered with dashes then it was originally a text format and if it was entered without the dashes, then excel interpreted it as a number. When formatted as a special (phone number), it appears in the spreadsheet as special format, but in the original formula bar it appears as originally entered. It is the same as with currency. If you enter a number without a currency symbol, but format it as currency, then it appears correctly in the spreadsheet but appears as a number without the currency symbol in the formula bar. Hope this helps you to understand the situation.

  9. #9
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: Numbers will not format despite using text to columns tool

    Thank you both. The data is entered by someone outside the company and sent to us. Do you happen to know if there's any way to change the original format quickly without having to reenter the data?

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Numbers will not format despite using text to columns tool

    Look at Post #6 above by Amoreno

  11. #11
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: Numbers will not format despite using text to columns tool

    Yes, I saw that. Thank you. The format painter matches the formatting of the cell but does not affect the original data (which is still a number string I guess). As a result, when I perform a Vlookup with a nested =mid formula it extracts the wrong digits from the phone number. The Vlookup should extract the area code from (###) but fails to do so with any of the cells where the original data is still a string and does not have the (###) for area code but rather ###.

    Thank you again. Sorry for not being clear enough.

  12. #12
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Numbers will not format despite using text to columns tool

    I think in addition to formatting as noted in post #6 and #10, you first need to convert the text entries to numbers and then format. So in B2 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Now apply your phone number formatting to col-B

  13. #13
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: Numbers will not format despite using text to columns tool

    It works! Thank you to everyone. I really appreciate the input and suggestions.

  14. #14
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Numbers will not format despite using text to columns tool

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  15. #15
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: Numbers will not format despite using text to columns tool

    Well, it solved the old issue and created a new issue but I will try to solve myself.

  16. #16
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Numbers will not format despite using text to columns tool

    Thanks for the feedback and reputation points If you do need help with the new issue then open a new thread and someone will help.

+ 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. VBA to convert a range from numbers to numbers stored as text (Text to Columns)
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-06-2017, 05:50 PM
  2. [SOLVED] Format Spreadsheet - split text columns, rearrange columns, conditional format
    By lacke81c in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2015, 11:50 AM
  3. [SOLVED] Change imported numbers in text format to numbers
    By navialivad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2014, 02:30 PM
  4. Need Numbers to Concatenate to Number Format, Not Text Format
    By DJH in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-08-2014, 01:01 AM
  5. Replies: 6
    Last Post: 01-07-2013, 08:58 PM
  6. Changing numbers from text format to number format
    By garden_gnome in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2012, 12:48 PM
  7. Convert numbers from text format to number format
    By merlin68 in forum Excel General
    Replies: 4
    Last Post: 04-12-2005, 09:06 AM

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