Excel values incorrectly convert to 5 digit number

Johnny4strings

New Member
Joined
Nov 9, 2018
Messages
33
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:

BASESeries
14/6111
43349337
14/8510
15/8886

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

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

BASESeries
14/6111
43349 337
14/8510
15/8886

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

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

BASESeries
14/6111
6-Sep 337
14/8510
15/8886

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

I convert the BASE column to fraction and get the following:
BASESeries
14/6111
43349 337
14/8510
15/8886

<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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,814
Have you tried formatting the cells as Text?

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

Johnny4strings

New Member
Joined
Nov 9, 2018
Messages
33
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
If you want 9/6 as text you can use apostrophe '9/6
 

Johnny4strings

New Member
Joined
Nov 9, 2018
Messages
33
That could work---any way I can add the apostrophe to the "9/6" in a separate helper column, perhaps?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 9, 2018
Messages
33
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:
BASESeries
14/6111
'43349 337
14/8510
15/8886

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
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:
BASESeries
14/6111
'43349 337
14/8510
15/8886

<tbody>
</tbody>
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

New Member
Joined
Nov 9, 2018
Messages
33
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,082,570
Messages
5,366,364
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top