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]

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

	A​	B​	C​
1​	BASE
2​	43265​		6/14
3​	43349​		9/6
4​	43326​		8/14
5​	43327​		8/15

Sheet: Sheet2

 

[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