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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Upvote 0
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.
 
Upvote 0
If you want 9/6 as text you can use apostrophe '9/6
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top