How to change a formatted fraction into a decimal?

Welcome to the forum.

1/16 is being read by Excel as a date, hence the date serial number.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

1/16 is being read by Excel as a date, hence the date serial number.

I know that Excel has a built in preference for interpreting this as a date over a fraction, but I have long wished that this was better controlled by the user. Maybe someday someone designing a spreadsheet will decide that the choice to see fractions before dates ought to be available to us. Until then, we have to work with what we have.

Knowing that spreadsheets so strongly prefer to see dates before fractions, the only solutions I see involve extracting the numerator and the denominator from the text string, perform the division, and store the result. I see text based solutions using "/" and "X" as delimiters to get the numerator and denominator, then divide. Personally, I prefer Google Sheets SPLIT() function for this (MSFT is developing a similar function for Excel that is currently behind a subscription wall). =SPLIT(A1,"X/") will divide the text into three cells. Then divide the first by the second. =INDEX(SPLIT(A1,"X/"),1)/INDEX(SPLIT(A1,"X/"),2). Apply any number formatting you desire ("#/##" to see the fraction, 0.00% to see it as a percent or whatever you like).

That's probably how I would do this. Granted, it isn't quite compatible with Excel, but there are equivalent approaches in Excel using Excel's built in text manipulation functions to get the same effect. (windknife's solution, for example).

Originally Posted by windknife

=iferror(--(left(c1,find("/",c1)-1))/--(mid(c1,find("/",c1)+1,find("x",c1)-find("/",c1)-1)),"")

This worked! Had to change the x to uppercase though. Can someone explain to me what the -- is for? I understand the iferror.
Also why find instead of search?


@MrShorty

Yea I suppose I will have to do this. Does Google Sheets see fractions before decimals?

I am also curious to hear more about this Split function. How does Split(A1,"X/") split into 3? By looking at the function I would imagine it searches A1 for "X/" and splits it into 2 parts. Before "X/" and after "X/"....Though typing this out made me realize that the "X/" has to go somewhere which would explain the 3rd. Am I correct?
Index I assume treats the expression like an array and lets you select a specific item?

Is this in fact a Google Sheets query?

Everything I know about Google Sheets SPLIT() function I learned here: https://support.google.com/docs/answer/3094136 Note that the behavior you claim to expect (use the entire X/ string as the delimiter) is the behavior you would get if you put FALSE in the optional "split by each" (third) argument. In my example, I omitted the third (and fourth) argument, so it defaults to TRUE which causes Sheets to split the string at each character within the delimiter string. In the A1 case, the output of the SPLIT() function will be 1,16,010. Then, as you note, you can use the INDEX() function to return one element from that array.

As far as my limited experience with Sheets, Google also chooses to see dates before fractions. I'm not aware of a spreadsheet that has an option for preferentially seeing fractions before dates. Maybe someday...

For reference (if/when you gain access to it), here's MSFT's/Excel's TEXTSPLIT() function, which isn't exactly the same as Sheet's SPLIT() function, but is substantially similar: https://support.microsoft.com/en-us/...7-bdecace8a6e7