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.

 

[Johnny4strings]

Hi Akuini:

Column K has the data as it currently reside:

Below is my current macro in VBA:
..........................................................


Sub Refresh_Pivots()
'
' Refresh_Pivots Macro
' Refreshes all Pivots with new Import Data.
'




'
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 1
Range("U4").Select
ActiveWorkbook.RefreshAll
End Sub


...........................................................
-John

 

[Akuini]

Hi,Johnny4strings

Wait, let's try using formula, say data in A2:
1. in B2 use this formula:

=TEXT(A2,"m/d")

2. Copy B2 > paste as value.