[motom]

Hi everyone,
1.
I have a excel files contain 800+ sms,
one of the column is the date, like this 21/07/2010 20:48.
my question is, is there a way I can change them into format like this 2010.07.21 20:48 ?


2.
another problem is can I add 2 digits (61)in front of other numbers
e.g. 455445654, 456544565, 402156441...etc
change them to 61455445654, 61456544565, 61402156441...etc
these number are mobile no. so they are pretty much random


Thanks everyone

[Zack Barresse]

Hi there, welcome to the board!

1.
Yes. If it is an Excel-recognized date. Just select your data and format the cells. I'd tell you how to get there but not sure what version of Excel you have. You can either right click the selection and go to Format Cells, or just hit Ctrl + 1. Change the format to:
yyyy.mm.dd hh:mm

2.
Download and install ASAP Utilities (www.asap-utilities.com). Easiest way to do it. You can do this manually by formulas (separate column, copy, paste values) or by vba (just like how asap utilities does it).

HTH

[motom]

1. Fixed. It was a cvs file. after change it to xls, It can be changed easily. Thank you

2. could you tell me more about how to do it manually by formulas? Just want to learn more about excel

Thanks a lot for your help

[Zack Barresse]

In a separate column, enter something like this, assuming your data is in A1 and your formula is in an adjacent cell, like B1:

=61&A1

Then select that cell, hover your mouse over the bottom right corner until your mouse cursor turns into a bold plus sign, click, drag and copy down as far as you want to go (assuming your data is contiguous).

If you want to make the formula values static at this point, select the entire formula range, copy it (Ctrl + C), then paste special values (Alt + E, S, V, Enter).

But ASAP Utilities will do this for you.

[motom]

thank you for your help.. This forum rocks!

[motom]

Question2:
I installed ASAP Utilities, I'm not familiar with the software. which function should I use?
Also there's some number already have 61 in front, is there a faster way I can change those without 61 while those have 61 remain unchanged?

Thank you

[motom]

this seems work
=IF(LEFT(B2,2)="61",B2,VALUE("61"&B2))

where is the best place, can I learn this kind of formula?

[motom]

http://img.whirltools.com/i/92548477085572888184.jpg
another question:
the date are not sort.. I was wondering how can I sort them to oldest to newest?

[Zack Barresse]

Remember, if you use quotation marks in your formula, you'll get text, not a number, and could get a number stored as text. The formula you have should work ok, so long as the formula cell doesn't have a text format, but it's always something to look for, and a common cause of headaches. One way to coerce it to a number would be:

=0+A1
or
=1*A1
or
=--A1

As far as the date not sorting, ensure it is actually a date. There is no built-in function to check for an Excel-recognized date, not in a worksheet function anyway, there is in VBA. If you want this simple functionality in the worksheet as well, you can use a UDF like shown here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=323

Make sure they're actually a date, otherwise it will sort as text.

As far as the ASAP Utilities question, I don't know what version of Excel you have, but it's under the Text submenu, look for something that says Add characters before... I don't remember exactly what it says, but look around and you'll find it.

[motom]

I'm using Excel 2003

[Zack Barresse]

Look under the Text submenu:

[slurpee55]

one thing is that "sorting" text will not give you the results you want if you treat them as if they are numbers. Excel sorts text by the first digit, so if you have the numbers 1-100 as text, then sorting them will give you the order 1, 10, 100, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21,....