Split or delete part of a number

O

Oystein

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?
 
G

Guest

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.
 
O

Oystein

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.
 
G

Guest

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.
 
O

Oystein

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?
 
Top