Prevent Text/Numbers converting to Date

[Jbearstuff]

Hi, I see many questions on numbers being formatted to dates, but I have a problem with alpha-numeric items converting to dates.

I am importing data from a web page (*.htm,html) for excel 2003.
The data I am importing is like JAN14. This changes to 14-Jan (with custom or general format) and if I manually change to text format it becomes 40192. JAN14 is not a date, it is an item name. I have tried to fix this in Excel 2003 and 2007.

The item converts to a date upon opening. I have tried to manually fix the data using find/replace after changing the column to text, but it will change back to 14-Jan and the format will change to Custom/General. I have had to manually correct the data each time I run the report.

I can use a formula in another column to change 40192 to JAN14, but the number will change each year. Also, I would need to change the structure of the report.

Changing the original data of JAN14 is not an option.

Any help would be appreciated.

 

[pedro-egoli]

I was able to get Jan14 in cell A1 by clicking on that cell andtyping Jan14 into formula bar and formatting as text if thats of any use.

Pedro

 

[xenou]

It may help to format the worksheet cells as text prior to importing. It's an unfortunate item name.

 

[JoeMo]

Another approach might be to go with the flow and let Excel format your entries as "dd-mmm" 'dates'. Then create a helper column adjacent to your 'dates' column and use something like this (here I'm assuming you have the 'dates' in column A, say starting in A1) and pull it down to include all 'date' cells in column A:

=UPPER(RIGHT(TEXT(A1,"d-mmm"),LEN(TEXT(A1,"d-mmm"))-SEARCH("-",TEXT(A1,"d-mmm")))&LEFT(TEXT(A1,"d-mmm"),SEARCH("-",TEXT(A1,"d-mmm"),1)-1))

 

[Jbearstuff]

Thanks for the responses.

pedro-egoli, I am able to manually change the format and type it in, but I am looking for something to allow me to keep my report more automated plus there can be a lot of lines to change.

Xenou, I have formatted the column to text, but when I bring the data in it changes the format to general/custom and makes it a date, so I have to make the corrections.

JoeMo, I was trying not to make another column to translate the number, but your formula is good. I would need to make 1 change in the format. You have d-mmm and I would change to dd-mmm to get 2 digits if the item is like JAN02.

 

[xenou]

Can you say more about how you are importing the data from the web page? Is there a sample page or similar page to demonstrate this problem? What steps do you go through to get the data from the web page?

 

[Jbearstuff]

The data is produced in Oracle and sent in tab delimited format. I have inherited a macro that
1) opens the file
2) copies data to another excel workbook
3) parses the data, as default general format

I have now corrected the macro to change this column to text when it is parsed. It is now keeping the data as I need it.

The corrected text to column command is pasted below. Column 13 is now set to text (2) format, the rest of the columns are left to general (1) format.

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _
Array(11, 1), Array(12, 1), Array(13, 2), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1)), TrailingMinusNumbers:=True

Thanks for all the advice