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?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.
quot;Oysteinquot; gt; wrote in message
...
gt;I got a row that contains numbers with 11 digits, I need to delete or move
gt; out the last five so I can make the first six a date.
gt; Any good ideas?
gt;
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.
quot;Andyquot; wrote:
gt; Hi
gt;
gt; You could do it with a formula - in one go:
gt; =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
gt; This works for me (in English format).
gt;
gt; Andy.
gt;
gt; quot;Oysteinquot; gt; wrote in message
gt; ...
gt; gt;I got a row that contains numbers with 11 digits, I need to delete or move
gt; gt; out the last five so I can make the first six a date.
gt; gt; Any good ideas?
gt; gt;
gt;
gt;
gt;
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.
quot;Oysteinquot; gt; wrote in message
...
gt; hum... its been realy long since I did anything with Excel.. So I need a
gt; little help making that understandable. To be more specific of what I need
gt; help with:
gt; I got a column with about 200 numbers, they are all 11 digits, now I have
gt; to
gt; do something to make the first six digits in each number a date and remove
gt; the last digits. The two first numbers are date, number 3 4 are month
gt; and 5
gt; 6 are year. If that formula does that for all the numbers that would be
gt; nice, but I still need help on how I apply it to the numbers.
gt;
gt; Thanks, Oystein.
gt;
gt; quot;Andyquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; You could do it with a formula - in one go:
gt;gt; =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
gt;gt; This works for me (in English format).
gt;gt;
gt;gt; Andy.
gt;gt;
gt;gt; quot;Oysteinquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I got a row that contains numbers with 11 digits, I need to delete or
gt;gt; gt;move
gt;gt; gt; out the last five so I can make the first six a date.
gt;gt; gt; Any good ideas?
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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 quot;;quot; instead of quot;,quot; 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?
quot;Andyquot; wrote:
gt; Hi
gt; With your number in cell A2, paste my formula into an adjacent cell. See if
gt; it does what you want.
gt; If it does, you can simply fill this down (by dragging the blob in the
gt; bottom right-hand corner) to give you the same result with all of your other
gt; cells.
gt; When you have done this, select the whole range, go to Edit/Copy and then to
gt; Edit/Paste/Special/Values to fix the date in their cells.
gt;
gt; Andy.
gt;
gt; quot;Oysteinquot; gt; wrote in message
gt; ...
gt; gt; hum... its been realy long since I did anything with Excel.. So I need a
gt; gt; little help making that understandable. To be more specific of what I need
gt; gt; help with:
gt; gt; I got a column with about 200 numbers, they are all 11 digits, now I have
gt; gt; to
gt; gt; do something to make the first six digits in each number a date and remove
gt; gt; the last digits. The two first numbers are date, number 3 4 are month
gt; gt; and 5
gt; gt; 6 are year. If that formula does that for all the numbers that would be
gt; gt; nice, but I still need help on how I apply it to the numbers.
gt; gt;
gt; gt; Thanks, Oystein.
gt; gt;
gt; gt; quot;Andyquot; wrote:
gt; gt;
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; You could do it with a formula - in one go:
gt; gt;gt; =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
gt; gt;gt; This works for me (in English format).
gt; gt;gt;
gt; gt;gt; Andy.
gt; gt;gt;
gt; gt;gt; quot;Oysteinquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I got a row that contains numbers with 11 digits, I need to delete or
gt; gt;gt; gt;move
gt; gt;gt; gt; out the last five so I can make the first six a date.
gt; gt;gt; gt; Any good ideas?
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
out the last five so I can make the first six a date.
Any good ideas?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.
quot;Oysteinquot; gt; wrote in message
...
gt;I got a row that contains numbers with 11 digits, I need to delete or move
gt; out the last five so I can make the first six a date.
gt; Any good ideas?
gt;
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.
quot;Andyquot; wrote:
gt; Hi
gt;
gt; You could do it with a formula - in one go:
gt; =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
gt; This works for me (in English format).
gt;
gt; Andy.
gt;
gt; quot;Oysteinquot; gt; wrote in message
gt; ...
gt; gt;I got a row that contains numbers with 11 digits, I need to delete or move
gt; gt; out the last five so I can make the first six a date.
gt; gt; Any good ideas?
gt; gt;
gt;
gt;
gt;
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.
quot;Oysteinquot; gt; wrote in message
...
gt; hum... its been realy long since I did anything with Excel.. So I need a
gt; little help making that understandable. To be more specific of what I need
gt; help with:
gt; I got a column with about 200 numbers, they are all 11 digits, now I have
gt; to
gt; do something to make the first six digits in each number a date and remove
gt; the last digits. The two first numbers are date, number 3 4 are month
gt; and 5
gt; 6 are year. If that formula does that for all the numbers that would be
gt; nice, but I still need help on how I apply it to the numbers.
gt;
gt; Thanks, Oystein.
gt;
gt; quot;Andyquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; You could do it with a formula - in one go:
gt;gt; =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
gt;gt; This works for me (in English format).
gt;gt;
gt;gt; Andy.
gt;gt;
gt;gt; quot;Oysteinquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I got a row that contains numbers with 11 digits, I need to delete or
gt;gt; gt;move
gt;gt; gt; out the last five so I can make the first six a date.
gt;gt; gt; Any good ideas?
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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 quot;;quot; instead of quot;,quot; 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?
quot;Andyquot; wrote:
gt; Hi
gt; With your number in cell A2, paste my formula into an adjacent cell. See if
gt; it does what you want.
gt; If it does, you can simply fill this down (by dragging the blob in the
gt; bottom right-hand corner) to give you the same result with all of your other
gt; cells.
gt; When you have done this, select the whole range, go to Edit/Copy and then to
gt; Edit/Paste/Special/Values to fix the date in their cells.
gt;
gt; Andy.
gt;
gt; quot;Oysteinquot; gt; wrote in message
gt; ...
gt; gt; hum... its been realy long since I did anything with Excel.. So I need a
gt; gt; little help making that understandable. To be more specific of what I need
gt; gt; help with:
gt; gt; I got a column with about 200 numbers, they are all 11 digits, now I have
gt; gt; to
gt; gt; do something to make the first six digits in each number a date and remove
gt; gt; the last digits. The two first numbers are date, number 3 4 are month
gt; gt; and 5
gt; gt; 6 are year. If that formula does that for all the numbers that would be
gt; gt; nice, but I still need help on how I apply it to the numbers.
gt; gt;
gt; gt; Thanks, Oystein.
gt; gt;
gt; gt; quot;Andyquot; wrote:
gt; gt;
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; You could do it with a formula - in one go:
gt; gt;gt; =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
gt; gt;gt; This works for me (in English format).
gt; gt;gt;
gt; gt;gt; Andy.
gt; gt;gt;
gt; gt;gt; quot;Oysteinquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I got a row that contains numbers with 11 digits, I need to delete or
gt; gt;gt; gt;move
gt; gt;gt; gt; out the last five so I can make the first six a date.
gt; gt;gt; gt; Any good ideas?
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
