Convert Day and Month into Fraction

[Justair07]

Hi everyone,

I have some data that I exported from our ERP i.e, "COMP. .003 X 5/8 REV B, C, D E OR F".
I want to extract the the fraction from the string, i.e "5/8", into an independent cell.
To do this I preformed a Text to Columns and it worked great with one issue. The "5/8" is being looked at as a Date by Excel. I need to take the Date value "5/8" and make it a fraction "5/8".
Simply changing the format to fractions only gives me a very large whole number i.e "42863".

Can someone please help me with an easy way to accomplish this for a large amount of data?

As always, Thank you.

- Justin

[63falcondude]

You can use a formula such as this (below) to extract the date from the string in the given format.

=DATEVALUE(MID(A1,FIND("/",A1)-1,3))

You can also use Text to Columns if you prefer that.

Then format the cell as Date.

Excel sees dates as numbers. Specifically, 5/8/2017 is 42863 days after 1/0/1900 (a.k.a. 0)

[Justair07]

You can use a formula such as this (below) to extract the date in the given format.

=DATEVALUE(MID(A1,FIND("/",A1)-1,3)&"/"&YEAR(TODAY()))

Thank you for the reply but I need the value to be a fraction (5/8 aka .63), not a date.

[63falcondude]

Thank you for the reply but I need the value to be a fraction (5/8 aka .63), not a date.

I misunderstood. Try this:

=("0 "&MID(A1,FIND("/",A1)-1,3))+0

[Justair07]

I misunderstood. Try this:

=("0 "&MID(A1,FIND("/",A1)-1,3))+0

Very cool! I'd love to know how that works. Also, anyway to make it work for two digit fractions, i.e 25/32?

[protonLeah]

removed by bvj

[63falcondude]

Very cool! I'd love to know how that works.

If you want to enter a fraction into a cell, you enter "0 " (that is "zero space") before the fraction.

The FIND function finds the location of the leftmost "/" (minus 1) and the MID function pulls out the first 3 characters starting in that position.

You add a zero at the end to tell Excel to treat it as a number and not as text.

Also, anyway to make it work for two digit fractions, i.e 25/32?

Try this one:

=TRIM("0 "&MID(A1,FIND("/",A1)-2,5))+0

[Justair07]

Thank you so much! That is exactly what I needed falcon.

Ben, thank you as well. No I now how to properly use text to columns!

[63falcondude]

Thank you so much! That is exactly what I needed falcon.

Ben, thank you as well. No I now how to properly use text to columns!

You're welcome. Happy to help!