Custom number format

[samgraphics]

Hi, I've spent days looking for a way to create a number format like this; 01,05,09,13,15-08,10 or this way ; 1,5,9,13,15-8,10 where I can simply enter the numbers, 1591315810 or 01050913150810 and excel would format them that way because I have a lot of numbers to enter. I'm desperate.



Thanks in advance

[MrShorty]

Assuming it is always a 14 digit number, a number format code like 00","00","00","00","00"-"00","00 will display the number with those characters. If you enter something with fewer than 14 digits, Excel will fill in leading 0s to make 14 digits. If you enter something with more than 15 digits, Excel will drop everything past 15 digits (because Excel is limited to double precision and cannot handle numbers with more than 15 digits). Be clear that those extra characters in the number format code do NOT become part of the cell value (if you need to convert the cell value to this text string, a helper cell with a TEXT() function using that number format code will probably be necessary).

[samgraphics]

First of all man thank you so much for that QUICK reply. You saved my life. Seriously man, thank you. Second I did try that, but it was giving me something like this. 00,02,12,22,30-33,24 when I entered 21222303324 and I couldn't figure out what was happening, but thanks to your explanation I do now. again man thank you.

BTW it works like a charm. it would be nice if I didn't have to add a zero before the numbers below ten, but hey man, you just cut the time this would have taken me down by 2/3 so thank you.

[samgraphics]

MrShorty, I encountered a problem. while the format you gave me above is working there is an issue with using that approach. The reason I needed help with this is because I'm trying to compare a set of already drawn lottery numbers with a set from a different game. I copied the winning numbers from the lottery website and pasted them into excel which entered them without the zeros for numbers below ten like this.
8,9,10,19,34-1,2. the other set of numbers I have to enter manually that's why I needed help with the above format. Later the goal was to compare the two lists for numbers that are identical by using the conditional formating and highlighting duplicates. now because the both numbers are written in a different format excel doesn't see them as identical. The copied number is 8,9,10,19,34-1,2 the manually entered number is 08,09,10,19,34-01,02. Although they are the same excel doesn't see them that way. How should I resolve this? Thank you so much.

[MrShorty]

If I am understanding correctly, this is part of the problem I alluded to when I said that these extra characters do not become part of the cell's actual value.

I would suggest that, if the end goal of this is to compare two "sets" of lottery numbers, the most important step (before worrying about number formatting) is to understand the data entry steps well enough so that "equal" values will be seen as equal. I am having trouble visualizing exactly what is going on during these data entry steps, so I don't have a specific recommendation to make, but it looks like you need to do something different during one or the other of these data entry steps to make sure that equal entries can be seen as equal.

[AliGW]

Whilst I understand that you may be in a bit of a panic, don't tell us that it is URGENT, please. All your helpers here are volunteers giving freely of their own time: you haven't paid a penny for any assistance you get here, therefore expecting any form of preferential treatment, or seeming to, is inappropriate.

Thank you for your understanding. I have removed the urgency notice from your thread title.

Moderator

[samgraphics]

HI, I apologize for that. Didn't mean to be rude or inconsiderate or anything. I just copied this message from an email I sent in the excel community. I posted urgent in my first email because while you're right I haven't paid for anything I do appreciate the free help I'm receiving. This may not be urgent to anyone else, but it is urgent to me as it's a part of my job. I am thankful and grateful and wasn't aware I couldn't post "urgent" This is my first time here and wasn't aware of the rules. I apologize.

For the record I wasn't expecting any sort of preferential treatment. I just urgently needed some help. I'm sorry once again.

[AliGW]

it is urgent to me as it's a part of my job

If it is so very urgent, then perhaps you should consider using a paid service where you can get 24/7 and immediate help? Also, it's very unwise to rely on a team of volunteers for job-critical help.

The fact remains that it's not urgent to us, and we are the ones choosing whether or not to help you. That's the bottom line. Marking a thread as urgent will not get you help any faster. I hope you understand.

[samgraphics]

Hi, Thank you so much again for your help. Let me see if I can explain. I copied the numbers from the internet then pasted them into excel which kept the original formating and placed each number into a column of it's own. I used the concatenate function to combine all the numbers into a single cell then copied the numbers to a new file. I then manually entered the numbers I wanted to compare with them. That's the reason I was looking for a faster way to enter all the numbers without having to add a comma after each number.

After I read your reply today I was wondering if it was possible to use an "IF" statement to check to see how many digits there were then adjust the fomular you gave me to suit the number of digits there were, because the number of digits could be anywhere between 7 - 14? If it is possible what would it look like? Since I'm a novice to excel I have no idea how to do that.

Also you mentioned helper cell with a TEXT() function would I be able to do it that way, then convert the text back to numbers once I'm done entering all the numbers? If so, would you be able to show me what the function would look like?

Thank you so much again for your time, my appreciation is more than words can say.

Thank you

[samgraphics]

If it is so very urgent, then perhaps you should consider using a paid service where you can get 24/7 and immediate help? Also, it's very unwise to rely on a team of volunteers for job-critical help.

The fact remains that it's not urgent to us, and we are the ones choosing whether or not to help you. That's the bottom line. Marking a thread as urgent will not get you help any faster. I hope you understand.

I seem to be saying all the wrong things here, so let me just say, SINCERELY, I'm sorry. I will be more considerate in the future. Thank you for your understanding.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[MrShorty]

I'm sure there are many different ways to solve the problem. A lot depends on programmer's preferences and a solid understanding of the processes involved. From this side of the internet, I can only see the little bit of the project that you have described to us, and I cannot readily pull back and see the overall project as a whole, so it can be difficult to make recommendations.

As I explained, the key thing is to pay attention to cell values, not number formatting. As near as I can tell, you have two different data entry processes (one is copy and paste and the other is manually typed in). Each data entry processes begins with 7 one to two digit numbers, and your end goal is to be able to compare the resulting set of 7 numbers to see if they are the same. How am I doing so far at understanding the "big picture"?

For the manually typed in values, you suggest that you would like to be able to enter the 7 one to two digit numbers as a single up to 14 digit number (I don't immediately see a good way to avoid entering the insignificant 0s, so I think this will be easiest if you enter 14 digit numbers -- including the insignificant 0s as part of the manual entry step). Assuming that we don't change this manual entry process, I would look at the copy and paste process and see how to get it to result in an equivalent 14 digit number.

Currently, you indicate that the copy paste process yields a 1x7 range of cells, each with a 1 to 2 digit number. You indicate that you are currently combining those cells into something, but it is not clear to me exactly what that looks like. Considering the goal of getting a 14 digit number, I would probably:

1) Enter the numbers 1E12, 1E10, 1E8, 1E6, 1E4, 1E2, 1E0 into a convenient 1x7 range of cells.
2) use a SUMPRODUCT() formula to combine the numbers into a single 14 digit number =SUMPRODUCT(output of copy/paste, reference to the range of numbers entered in step 1).

Does that help at all?

[samgraphics]

MrShorty, Thank you so much for your help on this I really appreciate this. Sorry for the late reply.

Yes your assessment is correct. That is what I'm trying to do.

I attached a sample sheet. So the numbers in
column A are the numbers copied from the website and the numbers in column B are the numbers I entered manually. I want
to compare B with A and see if any are identical and if they are highlight them with a background color. I would also like to
find a way to format the background color of odd and even numbers. in a different sheet ofcourse not the same sheet that is
identifying identical matches.

I have tried to make sense of the instructions you left in your last reply but I ended up confusing myself, novice.

Thank you so much

[MrShorty]

In order to avoid "project creep", what exactly do you need help with? Up to now, it seems that the main focus has been the data entry step, and not any of the different analyses that you want to do. Do you need help with all of the different analyses, or are we still only worried about the data entry step?

If I understand the desired analyses, it seems to me that the end goal for both data entry methods is to end up with a 1x7 block of cells each with a one to two digit number. As near as I can tell, you are satisfied with the copy/paste procedure that you have, and the main question is how to do the manual data entry.

1) In the latest file, you have entered them as comma+dash delimited text strings, but I thought we were trying to avoid the delimiters. One advantage to using the delimiters is that it is a simple text to columns command to split the text into seven columns. Would it be easier to use a different delimiter? One of the characters from your 10 key entry pad (mine has +, -, *, /, . any of which would make a fine delimiter, easy 10 key data entry, and be easily split into 7 columns.
2) We had talked about entering as 14 digit numbers, and you seemed fairly satisfied with that. The main problem I see is that it seemed real easy to forget a leading 0 for the one digit numbers, which would be a source of data entry errors.
3) You could simply enter each one to two digit number directly into its own cell. For this option, I would probably go into Excel Options and find the "After pressing enter move selection..." option and specify "right". Then I could simply select my n row by 7 column selection, and enter each one to two digit number separated by enter, and Excel will put them in the appropriate cells.

What do you think? Any of those considerations help in deciding how to do the data entry?