# Sorting Two Digit Groups Within A Cell

Feb 18, 2009
I have a column of Cells.

Each cell has five, two digit number pairs with spaces between the pairs.

IE: 25 03 48 19 33

How can I get those number sets to sort numerically on the first digit, and still maintain the two digit grouping?

View 14 Replies
ADVERTISEMENT
Sep 21, 2006

I am trying to sort a spreadsheet in descending order of a certain column. I have been able to do so quite easily in the past. However, for some reason, when I am trying to sort it now, it is sorting based only on the first digit of the number in the cell. For example, the order after sorting may end up as 90, 9, 9, 82, 8, 8, 8, 74, etc. I don't know what happened to cause the change, or how to fix it.

View 6 Replies
View Related
Feb 28, 2014

I'm am trying to take multiple column-groups and sort them by product ID number.

I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.

I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".

starting point.xlsx

end product.xlsx

View 1 Replies
View Related
Jan 8, 2013

I am looking to sort this file by "start date", from oldest to newest start date.

There is 3 groups, each of them is for 1 Student, I don't want the data inside the group files to be sorted, I want the whole groups to be sorted according to there "title" start date (there title is the writing with bold character and thick borders)

My problem raise is that I can not attach each title to its actual block/group.

The result I'm looking for is being able to sort (while groups are all collapsed/minimized) the groups according to there start dates that is shown in the always visible title, once sorted the whole groups will move along with there titles to the right order.

View 1 Replies
View Related
Mar 30, 2007

I realize that the easier resolve to this request is to use Access & we are moving to that application.

I have included a worksheet as an example.

We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.

Not a normal sort because the values aren't entered in typical side-by-side row & column format.

View 10 Replies
View Related
Apr 3, 2008

I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?

View 4 Replies
View Related
Jun 13, 2014

How to calculate a check digit in excel. The details to calculate this are as follows:

All variables in the calculation are positive integers.

We take each integer of the pro number and multiply it by a value and sum them to get a total.

An Example: 8 Digit Pro Number: 66988757

Pro Number: 6 6 9 8 8 7 5 7

Position in the Pro Number: 1 2 3 4 5 6 7 8

Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.

Using our example pro again:

6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8)

6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2

54+ 48+ 63+48+40+28+15+14 = 310

Take the Sum of the previous calculation and divide it by 11

310/11 = 28

(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).

Figure a remainder by multiplying the quotient by 11 and subtracting from the sum.

Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2

Check Digit = 11- Remainder

Check Digit = 11 - 2 ---> Check Digit = 9

Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.

View 3 Replies
View Related
Aug 23, 2014

Imagine a cell has a value of "008281" at A1

I want to return the first digit of the 6 digits in A1 which will be "0" in A2.

I tried using the formulae in A2 with "=left(A1,1), it returns "8" instead.

View 2 Replies
View Related
Jan 10, 2009

How can I get one cell to increase by 1 after I change the date in another cell. The spreadsheet is used to keep track of Oxygen Bottles at the local Fire Dept. and I want the " Times Filled" cell to increase by 1 after the " Date Filled" cell is changed. I'm not sure if they key the date in or if they use a calender add-in pop up.

View 5 Replies
View Related
Apr 8, 2013

I have some alphanumeric mixed data which is from 15 digits to 18 digits. In each data there have 5 zeros in the middle. I want to keep all the alphanumeric to 15 digits only. It will delete the zeros not any other value which length is greater than 15 digits. If a length of a character is 17 digit it will delete 2 zeros & if it is 15 digit then keep it constant.

Like: AJSPP00000125467 change to AJSPP0000125467.

View 5 Replies
View Related
Feb 20, 2010

I am trying to get a formula to return the last digit from a number in a cell as a number.

If the cell (B3) contains a number such as 164 or 34 I want to return a value of 4 and have it be formated as a number.

I have tried =RIGHT(B3,1) and it returns a 4 but I cannot get another formula to recognize it as a number.

It shows up in the cell on the left side as text would and if I format it as a number nothing happens it is still on the left and the formula does not work.

View 7 Replies
View Related
Oct 14, 2013

I'm trying to search a column that has cells with various length strings. Included in the strange (at random locations) could be possibly be a 7 digit number starting with 4. I want a formula that counts the number of cells with contain that 7 digit number. Is this possible?

View 11 Replies
View Related
May 9, 2014

Image below has a 500 cells I want to delete the 12 digit number but the numbers that align to the 12 digit was in one cell so it is hard to do manually how to delete it

View 6 Replies
View Related
Mar 12, 2014

I have the following code which adds a consecutive digit to a repeating cell value in:

=a2&countif($A$2:A2,A2)

So, this formula has put in cell B2. This means, that if if A2:A5=1 for each row, then B2=11, B3=12, B4=13, etc.

What I'm trying to do is put "-" between the original value and the extra digit. So, B2 would instead look like 1-1, B3=1-2, B4=1-3, etc.

View 2 Replies
View Related
Dec 11, 2009

Split from Count Of Groups Where Last Character Is Unique

The A1 B1 C1 were not the data items in the table, but the cell addresses. The new title of the thread does not reflect what I am asking either. The focus of the question is the counting of groups, not counts of cell contents.

View 5 Replies
View Related
May 6, 2009

This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place.

Here is my

View 7 Replies
View Related
Aug 11, 2008

I am trying to write a formula that will return a result based on which list a cell is found in.

Column A comprises of aisle/position information. The values range from B01 to B65, C01 to C65 ... H01 to H65 (Like aisles/shelf positions in a supermarket). This is not a static list, as the information in column A will change from week to week. It will be sorted prior to trying to work with it. The first three characters of the values in A will be what I'm comparing to the list. There is a fourth character that determines further details about the item that are not pertinent to this issue.

I need to match the value in column A to one of the four groups of values below and have the correct color display in column F. The correct color would be the name of the list that the value is found in.

Blue (K2:K61) contains the values B01 through B25 and C01 through C25.

Red (L2:L80) contains the values F01 to F21, G01 to G23 and H01 to H35.

Yellow (M2:M95) contains the values B36 to B65, C45 to C65 and D23 to D65.

Green (N2:N124) contains the values E36 to E65, F34 to F65, G34 to G65 and H37 to H65.

It probably boils down to a nested IF statement that checks to see if LEFT(A2,3) is found in any of the four lists, but I just can't wrap my head around how to write it.

View 9 Replies
View Related
Oct 21, 2007

I am trying to find a way to sort a string of numbers within a cell in numerical order as they are entered,regardless in what order they are entered. Example 123,45,478,12 entered cell, and want it to sort in numerical order as it gets entered in the cell...12,45,123,478,4564

View 2 Replies
View Related
Mar 2, 2009

I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...

View 4 Replies
View Related
Jul 3, 2014

I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").

I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).

The statement I had was:

=if(A1>=50,1900+A1,2000+A1)

I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.

It seems like it should be pretty simple, but I don't know how to word it in a macro.

Starting Column Example:

12

13

14

99

11

[Code] ......

For some reason I can't get rid of the borders...

View 6 Replies
View Related
May 6, 2008

This is my first post but I have been using Ozgrid for awhile now. I am farily good with excel formulas but have just started with macros so bear with me if i dont understand what you mean at first.

I am looking for a way to copy rows our of sheet2 in the attached sheet based on the value in the segments column in sheet 2. The rows need to be paste into sheet3 (already has heading set up). The segments value is the number of times i need each row copied into the next sheet. The purpose of this is to split random length samples into 10cm incriments for study. For example, a 1.5m sample is taken so there should be 15 segments of 10cm each copied into sheet3.

Also, if possible, it would be nice for it to display the actual length of the segment after copied into sheet3 for cases where the length was not evenly divisible by 10. I have found several examples of row copying macros, but none that will copy a conditional number of hte same row based on a cell value. In the original data there are close to 4000 rows, but the number of rows will vary depending on the data source.

Another thought I had was if the total number of available rows is going to be exceded would it be possible to have the rows pasted into different sheets based on the rock type listed in the column?

View 9 Replies
View Related
Apr 25, 2014

I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:

Waltham MA 2453

convert to 'Waltham MA 02453'

Boston MA 2210

convert to 'Boston MA 02210'

CEDAR GROVE, NJ 7009-1174

convert to 'CEDAR GROVE, NJ 07009-1174'

TEANECK CITY, NJ 7666

convert to 'TEANECK CITY, NJ 07666'

View 3 Replies
View Related
Apr 15, 2008

I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.

Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).

Example:

98 --> 1998

99 --> 1999

00 --> 2000

01 --> 2001

View 9 Replies
View Related
Dec 10, 2009

sorting a worksheet where the data is within a single cell.

For example;

Cell G1: "American Express Settlement 090914 0000013"

I would like to sort the entire worksheet using this cell and being able to sort by 0000013 and not American Express.

View 9 Replies
View Related
Apr 16, 2009

Say cell A1 has 55

Cell B1 has the formula =A1

Sort only column A (the 55 now becomes in a different row)

Cell B1 chnages value because it is referencing cell A1 which has now a new value.

I want cell B1 to ALWAYS get the value from that orginal cell (i.e. 55) no mmatter where that cell gets located after sorting.

INDIRECT, and Nammed Ranges don't seem to do it.

View 8 Replies
View Related
Jul 9, 2008

I have a multiple Excel sort option question and I hope that someone can answer it. I have multiple rows and I want to sort rows together that match(text or value) in 3 different columns. If there is not a match, I would like Excel to delete the row. I have over 160,000 items and probably only 20,000 match and this is why it would be such a pain to do this manually.

View 9 Replies
View Related
Aug 22, 2008

I understand how to sort based on Ascending order and descending order but what if I have to sort based on cell value? For example, in Col. 'L' I have a "Type' header name and under that there are probably 10 different categories. Few of them are "Achieved", "Bypass" "Certifications", "Monitor",and "YTD" . I would like to know how can I sort all the rows with "Certifications" first and then comes rest of the data and finally at the buttom of the data is "Monitor".

View 9 Replies
View Related
Aug 28, 2008

Any way to get the formatting to stay with a row that has just been sorted (by formatting I mean cell shading and borders)?

I have a page full of information that will be updated periodically and needs to be sorted. However, I would like to be able to keep the cell shading & borders with the cell info it was originally used for.

View 9 Replies
View Related
Feb 26, 2009

I'm trying to sort a column of email addressees to determine how many have the extension .ac - basically I need to know how many student addresses are present in the column.

View 9 Replies
View Related
Mar 14, 2009

I am trying to sort a column after an item is removed.

Is is sorted by name. I remove the name and hit sort and it works fine but I am left with a blank cell. Is there anything I can do for this?

Here is the code.

Private Sub CommandButton1_Click()

Range("A1:A10").sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

View 9 Replies
View Related