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]

Wow yeah that would be great! I can definitely try it! Thank you for being so helpful; this has been a serious pain in my rear.

 

[Fluff]

If you are pasting the data into notepad, one option would be to save that as a text file & import it.
On the Data tab > From text > on step 3 select the "Base" column & then select Text

 

[Akuini]

Wow yeah that would be great! I can definitely try it! Thank you for being so helpful; this has been a serious pain in my rear.

Here's an example:
The data is in col A & the result in col C.
You're regional setting use month-day format right? So in the code I use format "mm/dd".

Sub a1077100a()
Dim r As Range, c As Range
Dim a As String, b As String

Set r = Range("A2", Cells(Rows.count, "A").End(xlUp))
For Each c In r
    If IsNumeric(c) Then
        tx = Format(c, "mm/dd")
        a = CLng(Split(tx, "/")(0))
        b = CLng(Split(tx, "/")(1))
        c.Offset(, 2) = "'" & a & "/" & b
    End If
Next
End Sub

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]BASE[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

43265​

[/td][td][/td][td]6/14[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

43349​

[/td][td][/td][td]9/6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

43326​

[/td][td][/td][td]8/14[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

43327​

[/td][td][/td][td]8/15[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[Akuini]

Ah, actually there is a much simpler solution.
Select the data>Right click > Number > Custom > then type m/d

 

[Johnny4strings]

HELL YEAH. Thanks for this-please check your inbox
-John

 

[Johnny4strings]

Thanks, though, unfortunately this doesn't work-cells in question now list as "9/6/2018" in the formula bar

 

[Akuini]

HELL YEAH. Thanks for this-please check your inbox
-John

Sorry, all discussion should be done here not via inbox.

 

[Johnny4strings]

Whoops, new here-no problem. Was just asking where I should try the code? Create a new module? I have one already to refresh some pivot tables I have.

 

[Akuini]

Whoops, new here-no problem. Was just asking where I should try the code? Create a new module? I have one already to refresh some pivot tables I have.

You copy paste the code below the existing one.
But you need to modified the code to suit your data layout. In what column is the data?

 

[Akuini]

Here's the revised code, I use format "m/d".
The data is in col A and the result (helper column) in col Z, you may change that to suit.

Sub a1077100b()
Dim r As Range, c As Range, q As Range

rr = Range("A" & Rows.count).End(xlUp).row
Set r = Range("A2:A" & rr)
Set q = Range("Z2:Z" & rr)
q.Value = r.Value
 For Each c In q
    If IsNumeric(c) Then
        c.Value = "'" & Format(c, "m/d")
    End If
Next

End Sub