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:

BASE	Series
14/6	111
43349	337
14/8	510
15/8	886

<colgroup><col><col></colgroup><tbody>
</tbody>

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

BASE	Series
14/6	111
43349 337
14/8	510
15/8	886

<colgroup><col><col></colgroup><tbody>
</tbody>

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

BASE	Series
14/6	111
6-Sep	337
14/8	510
15/8	886

<colgroup><col><col></colgroup><tbody>
</tbody>

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

BASE	Series
14/6	111
43349	337
14/8	510
15/8	886

<colgroup><col><col></colgroup><tbody>
</tbody>

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.