Excel importing text fil

[msnreddy]

Hi members,
i am trying to import a text file delimited with ? .
the text file looks like this

Name?SAL?Month/YearOfJoin?Designation
xxx?10000?03/03?SoftwareEngineer
yyy?20000?01/03?SoftwareEngineer
xyz?15000?05/03?SoftwareEngineer

when i import this from microsoft Excel the Month/YearofJoin field is converting to a date Cell and values are comming like 03-March
01-March
05-March
but in 03/03 , first 03 is month and second 03 is Year.
i dont want this cell should come like this , i want as it is looking in text file.
how can i get this

 

[sfvb]

I assume you're using the import wizard. After you set the delimter in step 2, go to step 3, highlight the Month/YearOfJoin column, then choose the Text radio button.

 

[SkipVought]

Hi,

First, you need to understand how Excel stores dates. Dates are really numbers -- today, 6/21/2003 is really stored as 37793, which is relative to 1/1/1900.

What you are wanting Excel to interpret is a string like "03/03" which YOU know is March 2003, BUT March 2003 is NOT a date to Excel -- March 1 2003, however is.

Sh here's what we have to do.

1. In the Import Wizard, we DO NOT want the DATE column to be interpreted as a data -- rather, as TEXT. Then, it will come in EXACTLY as it is, "03/03"

2. After the data has been parsed into columns, we want to make dates out of ttat text as follows.
a. In an adjacent empty column enter the following formula in ROW 2

      =DATEVALUE(LEFT(C2,2)&"/1/20"&RIGHT(C2,2))

assuming that all the dates are in this century (If not, let me know and I'll modify the formula if you are not able to)
b. Copy this formula down as far as needed
c. Copy this range of formulas and Paste Special/Values into the column containing the "03/03" dates
d. Format these cell with the date format that you choose, either a built-in format or a custom format, and...

VOLA!

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com