Excel help PLEASE

[rajncajn]

In my line of work we use something called a basic location number. The number represents the deck level, frame number & relationship to center line of a ship & usually will look something like 01-26-1. When I paste the numbers they are fine as is, but when I try to sort them it recognizes some as dates & some not & screws up the order.

 

So 1-25-0 comes up as 01/25/2000 yet 1-254-0 stays as is.

 

I am really in a bind here because I've spent several hours trying to get a report out and I can't get the data to cooperate.

 

[keggerz]

have you changed the formatting for the cells? If not change it to TEXT under formatting

Text works and it sorts too

 

EDIT: but if the middle number is 3 digits it sorts by the 1st two...

IE:

1-24-0

1-254-0

1-26-0

2-12-1

2-121-1

2-14-2

Edited September 15, 2009 by keggerz

[rajncajn]

have you changed the formatting for the cells? If not change it to TEXT under formatting

Text works and it sorts too

 

EDIT: but if the middle number is 3 digits it sorts by the 1st two...

IE:

1-24-0

1-254-0

1-26-0

2-12-1

2-121-1

2-14-2

Even when I change the formatting to text it still sees it as a date. It no longer gives me the error, but still sorts it as such. I tried doing a find & replace, replacing the "-" with another character. The sort works after that, but when I try to do a find & replace to put the "-" back in is auto corrects it to a date.

Edited September 15, 2009 by rajncajn

[keggerz]

Even when I change the formatting to text it still sees it as a date.

i entered numbers and when I changed it didnt seem to work but when I re-entered the numbers it did

[Gonkis]

Or goto format cells and pick custom. Select any option and then replace it with 00-00-0.

 

If you do this, you can simple type in the numbers, like 34121 and it will automatically format it 34-12-1 and sort it like a number. If you type in 3121, it will translate to 03-12-1 (note leading zero). If you don't care of about zeroes, you can put ##-##-# in the format. You are basically telling excel to keep the number formatting (xx-xx-x) the same.

 

Hope that helps.

[Gonkis]

It helps greatly if the numer of digits before and after the dashes is consistent, even with leading zeroes. the number of actual data places should be the same, no?

[rajncajn]

Here is the entire column, if you paste this into Excel you should see that the first 283 rows give errors that say they are dates. Even if you change the format to text & try to sort it still keeps the current order:

 

(note- "02" & "2" etc. are different, but can be sorted together)

 

1-25-0

1-25-0

1-26-0

1-26-0

2-3-0

2-3-0

2-8-0

2-8-0

2-20-0

2-20-0

02-28-0

02-28-0

3-5-0

3-5-0

3-8-0

3-8-0

3-18-0

3-18-0

3-19-0

3-19-0

3-22-0

3-22-0

3-25-0

3-25-0

1-2-1

1-2-1

1-3-1

1-3-1

1-11-1

1-11-1

1-14-1

1-14-1

1-17-1

1-17-1

1-18-1

1-18-1

1-19-1

1-19-1

1-20-1

1-20-1

1-21-1

1-21-1

1-22-1

1-22-1

1-23-1

1-23-1

1-24-1

1-24-1

01-24-1

01-24-1

01-25-1

01-25-1

1-26-1

1-26-1

01-26-1

1-26-1

01-26-1

1-28-1

01-28-1

01-28-1

1-30-1

01-30-1

1-30-1

01-31-1

01-31-1

2-3-1

2-3-1

2-6-1

2-6-1

2-6-1

2-6-1

2-7-1

2-7-1

2-8-1

2-8-1

2-11-1

2-11-1

2-12-1

2-12-1

2-14-1

2-14-1

2-15-1

2-15-1

02-23-1

02-23-1

2-25-1

2-25-1

2-26-1

2-26-1

2-28-1

2-28-1

3-5-1

3-5-1

3-7-1

3-7-1

3-9-1

3-9-1

3-10-1

3-10-1

3-12-1

3-14-1

3-14-1

3-16-1

3-16-1

3-17-1

3-17-1

3-21-1

3-21-1

3-22-1

3-22-1

3-24-1

3-24-1

03-28-1

03-28-1

4-10-1

4-11-1

4-16-1

4-19-1

4-19-1

4-19-1

4-19-1

4-26-1

1-2-2

1-8-2

1-8-2

1-8-2

1-8-2

1-18-2

1-19-2

1-19-2

1-21-2

1-21-2

01-24-2

1-24-2

1-24-2

01-25-2

01-25-2

01-26-2

01-26-2

1-26-2

1-26-2

01-27-2

01-27-2

1-28-2

01-30-2

1-30-2

01-30-2

1-30-2

1-31-2

1-31-2

01-31-2

01-31-2

1-31-2

1-31-2

2-1-2

2-1-2

2-3-2

2-3-2

2-8-2

2-8-2

2-9-2

2-9-2

2-10-2

2-10-2

2-11-2

2-11-2

2-14-2

2-14-2

2-15-2

2-15-2

2-15-2

2-15-2

2-16-2

2-16-2

2-17-2

2-17-2

2-18-2

2-18-2

2-19-2

2-19-2

2-21-2

2-21-2

2-21-2

2-21-2

2-22-2

2-22-2

2-23-2

2-23-2

2-26-2

3-3-2

3-6-2

3-8-2

3-10-2

3-12-2

3-13-2

3-13-2

3-15-2

3-15-2

3-17-2

3-17-2

3-18-2

3-18-2

3-19-2

3-19-2

3-20-2

3-20-2

3-21-2

3-23-2

3-23-2

3-24-2

3-24-2

03-28-2

03-28-2

03-29-2

3-30-2

3-30-2

03-31-2

03-31-2

4-13-2

4-20-2

4-20-2

4-25-2

4-25-2

4-26-2

1-19-3

1-19-3

1-22-3

1-22-3

1-24-3

1-24-3

01-25-3

01-25-3

01-26-3

01-26-3

1-26-3

01-30-3

01-30-3

1-30-3

2-11-3

2-11-3

2-15-3

2-15-3

2-26-3

2-26-3

3-7-3

3-10-3

3-10-3

3-17-3

3-17-3

1-21-4

01-24-4

01-24-4

1-24-4

1-24-4

1-30-4

1-31-4

1-31-4

2-14-4

2-14-4

2-16-4

2-16-4

2-19-4

2-19-4

2-22-4

2-22-4

02-29-4

02-29-4

3-13-4

3-13-4

3-18-4

3-18-4

3-23-4

3-23-4

4-26-4

1-24-5

1-24-5

1-30-5

1-30-5

2-26-5

2-26-5

2-19-6

2-19-6

1-30-7

01-32-2

01-32-2

01-34-1

01-34-1

01-35-2

01-35-2

01-36-1

01-37-1

01-37-1

01-37-2

01-37-2

01-39-0

01-39-0

01-39-1

01-39-1

01-39-2

01-39-2

01-40-0

01-40-0

01-40-1

01-40-1

01-40-2

01-40-2

01-40-4

01-41-2

01-46-1

01-46-1

01-49-1

01-49-1

01-54-2

01-54-2

02-29-1

02-29-1

02-29-2

02-29-2

02-30-1

02-30-1

02-31-1

02-31-1

02-32-2

02-32-2

02-36-1

02-38-1

02-38-1

02-38-2

02-38-2

02-38-3

02-39-1

02-39-1

02-39-2

02-39-2

02-40-0

02-40-0

02-41-0

02-41-0

02-44-0

02-44-0

02-44-2

02-44-2

02-46-1

02-47-1

02-47-1

02-50-2

02-50-2

02-51-1

02-51-1

02-51-2

02-51-2

03-36-0

03-38-2

03-38-2

03-38-2

03-44-1

03-44-2

04-32-0

04-32-0

1-32-0

1-32-0

1-32-1

1-33-1

1-33-1

1-34-1

1-34-1

1-34-3

1-34-3

1-35-1

1-35-1

1-35-3

1-35-3

1-36-1

1-36-1

1-36-3

1-36-3

1-36-5

1-36-5

1-37-1

1-37-1

1-37-2

1-37-2

1-37-4

1-37-4

1-37-6

1-37-6

1-38-2

1-38-2

1-38-2

1-38-2

1-40-2

1-40-2

1-40-4

1-40-4

1-41-1

1-41-1

1-41-2

1-41-2

1-41-3

1-41-4

1-42-1

1-42-1

1-42-1

1-42-1

1-42-2

1-44-1

1-44-1

1-44-1

1-44-1

1-45-1

1-45-1

1-45-1

1-45-1

1-46-1

1-46-1

1-46-2

1-47-1

1-47-1

1-48-1

1-48-1

1-50-1

1-50-1

1-50-1

1-52-1

1-54-0

1-54-0

1-54-1

1-54-1

1-54-2

1-54-2

1-54-4

1-54-4

1-57-0

1-57-0

1-57-1

1-57-1

1-57-2

1-57-2

1-57-4

1-57-4

1-57-4

1-57-6

1-57-6

1-58-0

1-58-0

1-65-1

1-65-1

1-65-2

1-65-2

2-30-2

2-30-2

2-31-1

2-31-2

2-34-0

2-34-0

2-34-1

2-34-1

2-34-2

2-34-2

2-34-3

2-34-3

2-35-1

2-35-1

2-36-1

2-36-1

2-36-1

2-36-1

2-39-2

2-39-2

2-39-4

2-39-4

2-40-1

2-40-1

2-40-3

2-40-3

2-40-5

2-40-5

2-40-6

2-40-6

2-41-2

2-41-2

2-41-4

2-43-1

2-43-1

2-43-3

2-43-3

2-43-5

2-43-5

2-44-1

2-44-1

2-44-2

2-44-2

2-44-4

2-44-4

2-45-1

2-45-1

2-45-2

2-45-2

2-45-3

2-45-3

2-45-5

2-45-5

2-46-2

2-46-2

2-46-4

2-46-4

2-47-1

2-47-1

2-48-2

2-48-2

2-48-4

2-48-4

2-49-1

2-49-1

2-49-2

2-49-2

2-50-1

2-50-1

2-50-2

2-50-2

2-50-3

2-50-3

2-50-4

2-50-4

2-51-1

2-51-1

2-51-2

2-51-2

2-51-4

2-51-4

2-52-2

2-52-2

2-52-2

2-52-2

2-52-4

2-52-4

2-53-1

2-53-1

2-53-2

2-53-2

2-53-4

2-53-4

2-54-2

2-54-2

2-62-1

2-64-2

2-64-2

2-65-2

2-65-2

2-65-4

2-65-4

2-65-6

2-75-1

2-75-2

2-A-2

2-A-2

3-35-1

3-35-2

3-36-1

3-38-1

3-39-1

3-40-1

3-40-2

3-44-1

3-44-1

3-45-2

3-45-2

3-45-2

3-45-2

3-46-1

3-46-2

3-46-2

3-47-2

3-48-2

3-48-2

3-48-2

3-49-2

3-50-1

3-50-1

3-50-1

3-50-1

3-53-2

3-53-2

3-54-0

3-54-0

3-54-0

3-54-1

3-54-1

3-54-1

3-54-1

3-54-2

3-54-2

3-54-2

3-54-2

3-54-3

3-54-3

3-54-4

3-54-5

3-54-7

3-54-7

3-58-0

3-59-1

3-59-2

3-60-0

3-60-1

3-60-2

3-65-0

3-65-0

3-65-1

3-65-1

3-65-2

3-69-1

3-73-2

3-74-1

3-74-1

3-76-0

4-33-1

4-33-2

4-33-3

4-33-4

4-33-6

4-35-1

4-35-2

4-37-2

4-37-4

4-38-2

4-38-4

4-39-2

4-39-4

4-41-1

4-44-1

4-44-1

4-44-2

4-44-3

4-45-1

4-45-1

4-46-1

4-49-2

4-49-4

4-50-1

4-53-1

4-53-2

4-54-1

4-54-3

4-55-1

4-59-1

4-59-2

4-59-4

4-60-1

4-62-2

[rajncajn]

Or goto format cells and pick custom. Select any option and then replace it with 00-00-0.

 

If you do this, you can simple type in the numbers, like 34121 and it will automatically format it 34-12-1 and sort it like a number. If you type in 3121, it will translate to 03-12-1 (note leading zero). If you don't care of about zeroes, you can put ##-##-# in the format. You are basically telling excel to keep the number formatting (xx-xx-x) the same.

 

Hope that helps.

If only I could do that. The first number represents the deck level & any deck that is considered above the Main Deck level is represented with a leading zero.

[Gonkis]

02 and 2 are different? Ouch.

 

I need to try to paste it so it doesn't automatically change to a date and then the custom number format should work. Right now, it automatically assumes a date and the custoemr formatting formats the samn date serial number excel uses. I hate that serial number date crap.

[rajncajn]

Well, I've gotten the data order I need, but I had to open the file with Access to do it. Once the order is set I can paste it back to Excel without it auto-correcting the numbers to dates. It works, but given the nature of my job it would be wonderful to be able to do it without having to jump through so many hoops.

[Gonkis]

Nice!

 

Guess I should get back to work now...

[gbpfan1231]

One option might be to convert the one filed to three seperate columns then you could sort by each column? You could use the functions of =right, =left, or =mid to do that? You could create a template and then cut and past your raw data in and then just make sure your foumulas match the number of rows.

 

Might take a bit of time to setup the first time but then you are golden.

[Ursa Majoris]

One option might be to convert the one filed to three seperate columns then you could sort by each column? You could use the functions of =right, =left, or =mid to do that? You could create a template and then cut and past your raw data in and then just make sure your foumulas match the number of rows.

 

Might take a bit of time to setup the first time but then you are golden.

This

[gbpfan1231]

This

????

[Ursa Majoris]

????

Shorthand for "correct answer"

[gbpfan1231]

Shorthand for "correct answer"

Sweet!!!

[rajncajn]

One option might be to convert the one filed to three seperate columns then you could sort by each column? You could use the functions of =right, =left, or =mid to do that? You could create a template and then cut and past your raw data in and then just make sure your foumulas match the number of rows.

 

Might take a bit of time to setup the first time but then you are golden.

Text to columns would work to get the data in order, but since the data will be dumped into a web-based database it has to be submitted in a certain format. I would have to recombine the separated columns back into one with the "-" separator which I have no clue how to do or if it's possible.

[gbpfan1231]

Text to columns would work to get the data in order, but since the data will be dumped into a web-based database it has to be submitted in a certain format. I would have to recombine the separated columns back into one with the "-" separator which I have no clue how to do or if it's possible.

=CONCATENATE(X,Y,Z)

 

For example:

A1 = 01

B1= 123

 

=CONCATENATE(A1,"-",B1) would equal 01-123

[gbpfan1231]

Text to columns would work to get the data in order, but since the data will be dumped into a web-based database it has to be submitted in a certain format. I would have to recombine the separated columns back into one with the "-" separator which I have no clue how to do or if it's possible.

Be careful with Text to columns - it looks like your data does not all "line" up. For exaple is the 4th digit always a dash or is it always the start of the second part of your information?

 

Text to columns will just put a straight line down your data and might "split" your data where you don't want it split.

[rajncajn]

=CONCATENATE(X,Y,Z)

 

For example:

A1 = 01

B1= 123

 

=CONCATENATE(A1,"-",B1) would equal 01-123

 

Cool trick, but when I do the text to columns it changes the "01" to "1."

[gbpfan1231]

 

Cool trick, but when I do the text to columns it changes the "01" to "1."

If you used the =left formula would it also drop off "0"?

 

Is this a huge file? I could PM you my e-mail and I could look at it for you. Let me know.

[rajncajn]

Be careful with Text to columns - it looks like your data does not all "line" up. For exaple is the 4th digit always a dash or is it always the start of the second part of your information?

 

Text to columns will just put a straight line down your data and might "split" your data where you don't want it split.

I use the "-" as the delimiter rather than a fixed width. I actually use that method quite often when dealing with these numbers, but in this case they have to be in that format.

[rajncajn]

If you used the =left formula would it also drop off "0"?

 

Is this a huge file? I could PM you my e-mail and I could look at it for you. Let me know.

Not sure what that means.

 

The file isn't big, but it does have over 600 entries so it's not something I would want to just manipulate manually. I do appreciate the offer, but at this point (since I got it to work in Access) I'm just looking for a solution to a continual headache. The problem comes up very often because we deal with these numbers a lot and if I could find a way to fix it then it would save me a lot of time & frustration in the future.

[gbpfan1231]

Not sure what that means.

 

The file isn't big, but it does have over 600 entries so it's not something I would want to just manipulate manually. I do appreciate the offer, but at this point (since I got it to work in Access) I'm just looking for a solution to a continual headache. The problem comes up very often because we deal with these numbers a lot and if I could find a way to fix it then it would save me a lot of time & frustration in the future.

Cell A1 = 01-123-789

 

In cell B1 type =Left(A1,2) the result should be 01 butr not sure ifit will drop the zero. The 2 in the formula tells Excel how many characters to bring back so if your formula read =Left(A1,4) the answer would be "01-1"

 

Well if you want me to check it out for the future the offer stands for 30 days

[rajncajn]

Cell A1 = 01-123-789

 

In cell B1 type =Left(A1,2) the result should be 01 butr not sure ifit will drop the zero. The 2 in the formula tells Excel how many characters to bring back so if your formula read =Left(A1,4) the answer would be "01-1"

 

Well if you want me to check it out for the future the offer stands for 30 days

Cool, will try that out.

 

One other related question. In the concatenate command, if I wanted to concatenate multiple cells of data & instead of putting a "-" in between I wanted to put a character return between the lines of data. What code would I use in place of the "-"?

 

example:

 

01-24-1 | PILOT HOUSE (VITAL SPACE) | 01-19-0-C

 

needs to be

 

01-24-1

PILOT HOUSE (VITAL SPACE)

01-19-0-C

 

rather than

 

01-24-1 01-19-0-C PILOT HOUSE (VITAL SPACE)