Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split or delete part of a number
I got a row that contains numbers with 11 digits, I need to delete or move
out the last five so I can make the first six a date. Any good ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split or delete part of a number
Hi
You could do it with a formula - in one go: =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2)) This works for me (in English format). Andy. "Oystein" wrote in message ... I got a row that contains numbers with 11 digits, I need to delete or move out the last five so I can make the first six a date. Any good ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split or delete part of a number
hum... its been realy long since I did anything with Excel.. So I need a
little help making that understandable. To be more specific of what I need help with: I got a column with about 200 numbers, they are all 11 digits, now I have to do something to make the first six digits in each number a date and remove the last digits. The two first numbers are date, number 3 + 4 are month and 5 + 6 are year. If that formula does that for all the numbers that would be nice, but I still need help on how I apply it to the numbers. Thanks, Oystein. "Andy" wrote: Hi You could do it with a formula - in one go: =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2)) This works for me (in English format). Andy. "Oystein" wrote in message ... I got a row that contains numbers with 11 digits, I need to delete or move out the last five so I can make the first six a date. Any good ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split or delete part of a number
Hi
With your number in cell A2, paste my formula into an adjacent cell. See if it does what you want. If it does, you can simply fill this down (by dragging the blob in the bottom right-hand corner) to give you the same result with all of your other cells. When you have done this, select the whole range, go to Edit/Copy and then to Edit/Paste/Special/Values to fix the date in their cells. Andy. "Oystein" wrote in message ... hum... its been realy long since I did anything with Excel.. So I need a little help making that understandable. To be more specific of what I need help with: I got a column with about 200 numbers, they are all 11 digits, now I have to do something to make the first six digits in each number a date and remove the last digits. The two first numbers are date, number 3 + 4 are month and 5 + 6 are year. If that formula does that for all the numbers that would be nice, but I still need help on how I apply it to the numbers. Thanks, Oystein. "Andy" wrote: Hi You could do it with a formula - in one go: =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2)) This works for me (in English format). Andy. "Oystein" wrote in message ... I got a row that contains numbers with 11 digits, I need to delete or move out the last five so I can make the first six a date. Any good ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split or delete part of a number
I am a step closer now, thanks, worked ok on most numbers, but what I found
now was that I lost the 0 in start of the 11 digit numbers after making the number into an alphanumeric number, making some of the dates wrong. (I had to use ";" instead of "," to make the formula work) Got any good idea how to get the 0 back in the start of the numbers that have lost it? "Andy" wrote: Hi With your number in cell A2, paste my formula into an adjacent cell. See if it does what you want. If it does, you can simply fill this down (by dragging the blob in the bottom right-hand corner) to give you the same result with all of your other cells. When you have done this, select the whole range, go to Edit/Copy and then to Edit/Paste/Special/Values to fix the date in their cells. Andy. "Oystein" wrote in message ... hum... its been realy long since I did anything with Excel.. So I need a little help making that understandable. To be more specific of what I need help with: I got a column with about 200 numbers, they are all 11 digits, now I have to do something to make the first six digits in each number a date and remove the last digits. The two first numbers are date, number 3 + 4 are month and 5 + 6 are year. If that formula does that for all the numbers that would be nice, but I still need help on how I apply it to the numbers. Thanks, Oystein. "Andy" wrote: Hi You could do it with a formula - in one go: =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2)) This works for me (in English format). Andy. "Oystein" wrote in message ... I got a row that contains numbers with 11 digits, I need to delete or move out the last five so I can make the first six a date. Any good ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete part of a text string in every cell it appears in | Excel Discussion (Misc queries) | |||
I need help with an "If" Statement | Excel Worksheet Functions | |||
Part Number Lookup | Excel Worksheet Functions | |||
duplicate number | Excel Discussion (Misc queries) | |||
Part Number/Qty Consolidations | Excel Discussion (Misc queries) |