Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
I'm looking to sort Library of Congress call numbers in Excel. The format of
the cdall numbers is: Letters Number Period Letter Number and after that, perhaps more periods, letters and numbers! The problem seems to be that if I have (sorted): C3.A40 C25.C25 C124.D45 attempting to sort them as text would list C124.D45 C25.C25 C3.A40 Which is incorrect. I've been looking at the cell formatting features, and they seem to only allow me to change how the data is displayed, not how Excel thinks about the type of data. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
Try a helper column with this formula copied down, and sort on that
column....... =MID(A1,2,FIND(".",A1,1)-2)*1 hth Vaya con Dios, Chuck, CABGx3 "Casper" wrote: I'm looking to sort Library of Congress call numbers in Excel. The format of the cdall numbers is: Letters Number Period Letter Number and after that, perhaps more periods, letters and numbers! The problem seems to be that if I have (sorted): C3.A40 C25.C25 C124.D45 attempting to sort them as text would list C124.D45 C25.C25 C3.A40 Which is incorrect. I've been looking at the cell formatting features, and they seem to only allow me to change how the data is displayed, not how Excel thinks about the type of data. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
You will need to convert the codes into a fixed number of characters by
inserting leading zeros, so that your data becomes: C003.A40 C025.C25 C124.D45 assuming your first set of numbers is limited to 3 and your second set is 2 digits. You can use SEARCH( ) or FIND( ) to look for the first period and use this to extract the characters before it, then split and re-combine the elements in this way. You will need a few helper columns. Hope this helps. Pete Casper wrote: I'm looking to sort Library of Congress call numbers in Excel. The format of the cdall numbers is: Letters Number Period Letter Number and after that, perhaps more periods, letters and numbers! The problem seems to be that if I have (sorted): C3.A40 C25.C25 C124.D45 attempting to sort them as text would list C124.D45 C25.C25 C3.A40 Which is incorrect. I've been looking at the cell formatting features, and they seem to only allow me to change how the data is displayed, not how Excel thinks about the type of data. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
I think you already have answers for the "how to". I just wanted to say that
you are correct in saying there is a difference in how information is displayed on a spreadsheet and how Excel "thinks about it" internally. Always remember that - this difference in 'value' and 'formatted appearance' is often the souce of confusion. Take a simple date - it may look like 07-JUL-2006 11:58:11 a.m. but internally Excel is seeing it as 38909.4987384259 Kudos for realizing that such a difference exists. "Casper" wrote: I'm looking to sort Library of Congress call numbers in Excel. The format of the cdall numbers is: Letters Number Period Letter Number and after that, perhaps more periods, letters and numbers! The problem seems to be that if I have (sorted): C3.A40 C25.C25 C124.D45 attempting to sort them as text would list C124.D45 C25.C25 C3.A40 Which is incorrect. I've been looking at the cell formatting features, and they seem to only allow me to change how the data is displayed, not how Excel thinks about the type of data. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
Neither of those solutions work, exactly, because they assume too much about
how the data is structured. However, the idea of breaking the call number down into multiple fields holds, I think, the key to the problem. Incidentally, here's a description that's as good as any on how the LoC call numbers are structured: http://library.dts.edu/Pages/RM/Helps/lc_call.shtml So what I'd like to do is break the data down into three columns. The first column will have the initial 1-3 letters, the next will have the number section, and the third will have the period and everything after. I'll format columns 1 and 3 as text, and 2 as number, so I can tell Excel to sort first by the first column, next by the second, and next by the third. Now the challenge becomes, "How can I identify a variable number of letters and copy them to the first column, and variable number of numbers after letters and before a decimal point and move them to a second column, and the decimal point and everything after into a third column?" The third column ought to look like: =RIGHT(B1,(LEN(B1)-SEARCH(.,B1,1))) The first two are going to involve ISTEXT and ISNUMBER with IFs, I suspect, but I'm less sure how to structure those. Thanks in advance for any advice you can give! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
The structure of the data is AAA####.####
therefore we would try (not tested) for i = 2 to 8 if isnumeric(mid(Nr, i, 1)) then j = i part1=left(Nr, i-1) else if mid(Nr, i, 1)="." then part2=mid(Nr, j, i-j) part3=mid(Nr, i, 30) end if next i "Casper" wrote in message ... Neither of those solutions work, exactly, because they assume too much about how the data is structured. However, the idea of breaking the call number down into multiple fields holds, I think, the key to the problem. Incidentally, here's a description that's as good as any on how the LoC call numbers are structured: http://library.dts.edu/Pages/RM/Helps/lc_call.shtml So what I'd like to do is break the data down into three columns. The first column will have the initial 1-3 letters, the next will have the number section, and the third will have the period and everything after. I'll format columns 1 and 3 as text, and 2 as number, so I can tell Excel to sort first by the first column, next by the second, and next by the third. Now the challenge becomes, "How can I identify a variable number of letters and copy them to the first column, and variable number of numbers after letters and before a decimal point and move them to a second column, and the decimal point and everything after into a third column?" The third column ought to look like: =RIGHT(B1,(LEN(B1)-SEARCH(.,B1,1))) The first two are going to involve ISTEXT and ISNUMBER with IFs, I suspect, but I'm less sure how to structure those. Thanks in advance for any advice you can give! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
Add one more "Exit For" statement please
"PY & Associates" wrote in message ... The structure of the data is AAA####.#### therefore we would try (not tested) for i = 2 to 8 if isnumeric(mid(Nr, i, 1)) then j = i part1=left(Nr, i-1) else if mid(Nr, i, 1)="." then part2=mid(Nr, j, i-j) part3=mid(Nr, i, 30) end if next i "Casper" wrote in message ... Neither of those solutions work, exactly, because they assume too much about how the data is structured. However, the idea of breaking the call number down into multiple fields holds, I think, the key to the problem. Incidentally, here's a description that's as good as any on how the LoC call numbers are structured: http://library.dts.edu/Pages/RM/Helps/lc_call.shtml So what I'd like to do is break the data down into three columns. The first column will have the initial 1-3 letters, the next will have the number section, and the third will have the period and everything after. I'll format columns 1 and 3 as text, and 2 as number, so I can tell Excel to sort first by the first column, next by the second, and next by the third. Now the challenge becomes, "How can I identify a variable number of letters and copy them to the first column, and variable number of numbers after letters and before a decimal point and move them to a second column, and the decimal point and everything after into a third column?" The third column ought to look like: =RIGHT(B1,(LEN(B1)-SEARCH(.,B1,1))) The first two are going to involve ISTEXT and ISNUMBER with IFs, I suspect, but I'm less sure how to structure those. Thanks in advance for any advice you can give! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort Library of Congress call numbers in Excel?
Tested now
Sub M() Nr = ActiveCell j = 0 For i = 2 To 7 If (j = 0) * IsNumeric(Mid(Nr, i, 1)) Then j = i part1 = Left(Nr, i - 1) Debug.Print part1 Else If Mid(Nr, i, 1) = "." Then part2 = Mid(Nr, j, i - j) part3 = Mid(Nr, i, 30) Debug.Print part2 Debug.Print part3 Exit For End If End If Next i End Sub "PY & Associates" wrote in message ... Add one more "Exit For" statement please "PY & Associates" wrote in message ... The structure of the data is AAA####.#### therefore we would try (not tested) for i = 2 to 8 if isnumeric(mid(Nr, i, 1)) then j = i part1=left(Nr, i-1) else if mid(Nr, i, 1)="." then part2=mid(Nr, j, i-j) part3=mid(Nr, i, 30) end if next i "Casper" wrote in message ... Neither of those solutions work, exactly, because they assume too much about how the data is structured. However, the idea of breaking the call number down into multiple fields holds, I think, the key to the problem. Incidentally, here's a description that's as good as any on how the LoC call numbers are structured: http://library.dts.edu/Pages/RM/Helps/lc_call.shtml So what I'd like to do is break the data down into three columns. The first column will have the initial 1-3 letters, the next will have the number section, and the third will have the period and everything after. I'll format columns 1 and 3 as text, and 2 as number, so I can tell Excel to sort first by the first column, next by the second, and next by the third. Now the challenge becomes, "How can I identify a variable number of letters and copy them to the first column, and variable number of numbers after letters and before a decimal point and move them to a second column, and the decimal point and everything after into a third column?" The third column ought to look like: =RIGHT(B1,(LEN(B1)-SEARCH(.,B1,1))) The first two are going to involve ISTEXT and ISNUMBER with IFs, I suspect, but I'm less sure how to structure those. Thanks in advance for any advice you can give! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel viewer | Setting up and Configuration of Excel | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |