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!
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
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.
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.
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.
Just use the format painter on the cell format that you want to use and then apply to all the other cells
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).
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.
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?
Look at Post #6 above by Amoreno
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.
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:
Please Login or Register to view this content.
Now apply your phone number formatting to col-B
It works! Thank you to everyone. I really appreciate the input and suggestions.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Well, it solved the old issue and created a new issue but I will try to solve myself.
Thanks for the feedback and reputation pointsIf you do need help with the new issue then open a new thread and someone will help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks