How to add auto hyphenations to the numbers added in cell
Hello Friends,
Warm Greeting !!
I wanted to ask all the experts could it be possible to add auto hyphenations to the numbers added in cell for example if in A1 we write two numbers like 9735331786, 9735732438. Could it be possible that numbers automatically gets hyphens in A1 in the set of three, like 973-533-1786, 973-573-2438.
Please provide me non VBA or Macros solution.
Use "000\-000\-0000" as custom number formatting (Ctrl+1 > Custom)
or alternatively, if you want the output in another cell (and as a text string), you can use the formula,
=TEXT(A1,"000\-000\-0000")
Cheers,
berlan
Hello berlan,
Thanks for your assistance and timely response, you idea worked but I need to make bit alteration in the formula but when I enter two numbers in the cell formula stops working.
You gave me formula of "000\-000\-0000" but when ever I add any number in the cell the result appears "000\-000\-0000"
I removed inverted comas i.e ("") and added the following in customized box ie 000\-000\-0000 & result appeared was exactly what I needed but it stops working if in same cell I add two set of numbers like stated in my question i.e 9735331786, 9735732438.
How can I achieve the result of 973-533-1786, 973-573-2438 in same cell.
Please find the excel sheet on which I tried this formatting.
Originally Posted by berlan
you can use this formula
Formula:
Please Login or Register to view this content.
Thanks Salim,
But My purpose is not to create any new column or row for the result, If my formula is in A2 than I want it to be usable as well so that I can add information in that for example changing the formatting or adding some formula in data validation so that formula should go in background and result is achieved in same cell.
Regards,
Hi,
it's correct to use 000\-000\-0000 without any inverted commas (apologies for the confusion), but note that it can only be applied to numbers, or rather cells recognized as numbers.
Cell A5 in your sample file is recognized as a string which is why this cannot be applied. How is your data looking otherwise, how many sets of numbers do you have in your cells?
You could use a formula to first separate the different numbers (e.g. given comma as a separator) and then apply this on each set of numbers, but it would involve a longer and more complicated formula. Another way would be to use VBA.
I'm not sure that this can be achieved with formatting. As soon as you try to combine the two numbers into a single cell, the cell contents become a text string which is not effected by number formatting. You are welcome to review the different number format codes https://support.office.com/en-us/art...2-09fab54be7f4 but I do not think there is anything in there to describe a formatting option to break up text into pieces with a character between them.
If I understand what you are wanting, I think you are going to need to do something with a CONCATENATE() and TEXT() formula like Salim ali was suggesting. Maybe something like =CONCATENATE(TEXT(9735331786,"000-000-0000"),", ",TEXT(9735732438,"000-000-0000")).
Thanks for your quick reply Berlan and MrShorty,
My file is actually consisted of candidates whose database I am creating in excel it contains name ; email and phone numbers, some times when candidate gives their incomplete number we are not able to figure out if its not hyphenated and we have to type so fast that we don't have enough time to use hyphen after every three set, in one single cell we sometimes add one number and sometimes two numbers, I want that either during my typing hyphen should be added so that I can recognize the incomplete of additional digits in number. I could have designed my own VBA code but in our office systems macros and VBA settings are disabled so I want to discover some way out to overcome such a issue.
With this in mind, why are you trying to avoid the use of a helper column/row? It seems to me that the easiest solution to this kind of problem is to have one or more cells designated as "data entry" cells where you can enter the phone number(s). Then have a formula in an adjacent cell that will take the data entered and process it according to your needs. It is difficult to visualize what that formula will be without knowing exactly what your data entry will look like or what variations you need to be able to account for. Once one understands what the data will look like, what it should look like, and the different variations that need to be looked for, then he/she should be able to put together a function or a series of functions that can manipulate the entered text string accordingly.
My file has around 12 different column and by shrinking them I have made them to remain on screen without making me to scroll left and right
I am adding amount also for these candidates in which I get various other figures as per the plans, I cant afford more columns because I same formula will be applicable on many other column like SSN and zipcodes if for every column I will start making another formula column than I will be soon increasing 12 column to extra 6 column as well.
I don't have any solutions, but to make sure everything is clear, you need a solution that:
1) Does not use VBA -- worksheet formulas only.
2) Does not add columns, so the data must be entered and processed in the same cell.
3) A formatting option does seem to be the only possibility, but, as noted above, formatting is very limited for text string entries.
If I am understanding the thread, these are the main requirements. Normally, I would say that 1) and 2) are pretty close to mutually exclusive requirements, since number formatting seems to be out of the question. I don't know that exactly what you want will be possible. I think you will either need to relax the requirement against VBA, so that a macro can be written that will process the data as it is entered or after it is entered. Or you will need to use additional columns to process the data as it is entered.
Have I got the essential requirements correct?
Just to be clear - a cell can contain either data (text/numbers) OR a formula, it cannot contain both. It sounds to me that you want some magical formula to be working in thE cell while you enter data (text or numbers) into it.I want it to be usable as well so that I can add information in that for example changing the formatting or adding some formula in data validation so that formula should go in background and result is achieved in same cell.
If that is what you want, then pretty much, your opnly options are to enter the hyphens as you type, or to use VBA. ANY formatting you do, like has been suggested (berlan - Use "000\-000\-0000" as custom number formatting (Ctrl+1 > Custom)
will not actually change the contents of the cell, ONLY it's apperance.
The cell will still contain 123456789, even if the custom formatting makes it show 123-456-789
Also, almost any other formatting you apply (apart from colors/fonts) will replace that custom formatting
Last edited by FDibbins; 10-12-2015 at 09:35 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I see you don't want a new column you need formatting...
Maybe what you want instead is just conditional formatting that highlights the errors - make the cell red or something.
isn't making it hyphenated less effective than highlighting the error in red?
another option might be to have a second sheet which fixes the result with the formula and you enter on the front sheet (no loss in screen real estate - just the entry and result sheets are different).
Last edited by scottiex; 10-12-2015 at 09:19 PM.
And I assume hiding the extra helper columns is not an option?
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Attach a sample workbook. 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 use the paperclip icon to open the upload window.
View Pic
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
If you cannot use VBA or macros (XLM) or helper columns, you can't do what you want.
Remember what the dormouse said
Feed your head
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks