Excel values incorrectly convert to 5 digit number

[Johnny4strings]

Hello everyone!
I am having the toughest time formatting a spreadsheet with copy pasted data. For my purposes, I require all formatting to be "removed"; that "9/6, 12/1, 5/6", et al are not seen as dates, however like they are manually entered values with no format. Because Excel is thinking these numbers are dates, I am getting the 5 digit date codes and more after pasting (please see below):

I convert the BASE column to text and get the following:

[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BASE[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]14/6[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]43349[/TD]
[TD]337[/TD]
[/TR]
[TR]
[TD]14/8[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]15/8[/TD]
[TD]886[/TD]
[/TR]
</tbody>[/TABLE]

I convert the BASE column to general and get the following:

[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BASE[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]14/6[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD="align: right"]43349 337[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/8[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]15/8[/TD]
[TD]886[/TD]
[/TR]
</tbody>[/TABLE]

I enter "(9/6") manually and get the following:

[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BASE[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]14/6[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep[/TD]
[TD] 337[/TD]
[/TR]
[TR]
[TD]14/8[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]15/8[/TD]
[TD]886[/TD]
[/TR]
</tbody>[/TABLE]

I convert the BASE column to fraction and get the following:
[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BASE[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]14/6[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD="align: right"]43349 [/TD]
[TD]337[/TD]
[/TR]
[TR]
[TD]14/8[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]15/8[/TD]
[TD]886[/TD]
[/TR]
</tbody>[/TABLE]

Perhaps this is the fault of the data as it is coming from a source other than me (cannot change it), however, I have taken the data, placed it into notepad, then copied it into a new workbook that has been formatted to fraction, general, custom-doesn't matter; the values such as "9/6" are still seen as dates and end up populating as the five digit codes listed above, etc.

I have tried everything I know about formatting and helper columns, however Excel is not recognizing that I need to keep things "Manual"-and keep "9/6" as it is-nine, forward slash six.

Thanks for your time in helping me-Its really appreciated.

 

[mikerickson]

Have you tried formatting the cells as Text?

Its horrible for downstream arithmetic, but should keep "9/23" from being interpreted as a date.

 

[Johnny4strings]

Hi thanks for your response. Formatting to text was the first thing that I tried. Thankfully, I do not have to make calculations off this column other than for searching for instances.

 

[Akuini]

If you want 9/6 as text you can use apostrophe '9/6

 

[Johnny4strings]

That could work---any way I can add the apostrophe to the "9/6" in a separate helper column, perhaps?

 

[Akuini]

That could work---any way I can add the apostrophe to the "9/6" in a separate helper column, perhaps?

You mean the apostrophe is in a separate column? I don't think so

 

[Johnny4strings]

yes, I would have to add the apostrophe separately. I actually tried to just now with a ="'"&K2 formula, however this just resulted in the following:
[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]BASE[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]14/6[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]'43349 337[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/8[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]15/8[/TD]
[TD]886[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

yes, I would have to add the apostrophe separately. I actually tried to just now with a ="'"&K2 formula, however this just resulted in the following:
[TABLE="width: 168"]
<tbody>[TR]
[TD]BASE[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]14/6[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]'43349 337[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/8[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]15/8[/TD]
[TD]886[/TD]
[/TR]
</tbody>[/TABLE]

Hm, I think once Excel read the data as date you can't turn it back into fraction. If you have the source that still have the fraction format then it's better to modified the data (i.e adding the apostrophe) there.

 

[Johnny4strings]

Thanks for the help! I am going to check the source data and see if there are any issues with this-excel will just not allow me to modify this as it is, though Im still trying and hoping to get more perspective.

 

[Akuini]

Thanks for the help! I am going to check the source data and see if there are any issues with this-excel will just not allow me to modify this as it is, though Im still trying and hoping to get more perspective.

Wait, I was wrong, actually we can turn it back into fraction , but I don't know how to do it using formula, but maybe I can do it using vba. So if you're ok with vba maybe I can write the code.