LinkBack URL
About LinkBacks
I am trying to convert a column of fractions, 2 1/2" to decimal 2.5
If i remove the quote excel changes it to a date.
Thanks for your help!
I am trying to convert a column of fractions, 2 1/2" to decimal 2.5
If i remove the quote excel changes it to a date.
Thanks for your help!
Last edited by i.r.smith; 01-21-2015 at 04:47 PM.
Before you remove the quote, what is the format of the cell?
general format
If you will format your cells as fraction before entering the data, then Excel will automatically see these as fraction/mixed numbers and the cell value will be correct. After entering the data, you can then format as general or other fixed format and get the decimal values.
Originally Posted by shg
for some reason I can't attach another file. Here it is on my site.
www.thegallery.us/temp/fraction2.xlsx
I formatted column G as fraction
Then I copied column E and paste to column G
No change
Then I copied column E into notepad and removed all the quotes and copied from notepad to column I after I formated comumn I as fraction changed all to dates
deleted ...
Entia non sunt multiplicanda sine necessitate
any one have a solution?
www.thegallery.us/temp/fraction2.xlsx
I formatted column G as fraction
Then I copied column E and paste to column G
No change
Then I copied column E into notepad and removed all the quotes and copied from notepad to column I after I formated comumn I as fraction changed all to dates
Try this formula
=("0 "&SUBSTITUTE(E2,CHAR(34),""))+0
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
It worked with fractions less than 1
I get a #VALUE! with fractions more than 1
1 1/8" #VALUE!
1 1/8" #VALUE!
1 1/8" #VALUE!
1 1/8" #VALUE!
1 1/8" #VALUE!
7/8" 0.875
7/8" 0.875
7/8" 0.875
1 1/8" #VALUE!
1 1/8" #VALUE!
4" #VALUE!
Last edited by i.r.smith; 01-21-2015 at 10:37 PM.
I have sometimes wished Excel had a global "I prefer things with / to be seen as fractions" setting, because Excel currently just has too much preference for interpreting input as dates.
The easiest approach might be a combination of a function like Alkey suggests and a find replace. I think you will find that the find/replace method works just fine for the values greater than 1, because i n/d type number cannot be readily interpreted as dates, so Excel will naturally want to see those as fractions. A function like Alkey's will then work to fix those that are between 0 and 1.
Try this, it's a bit of a cheat but seems to work.
Open your file
Click Tools
Click Options
Look for the Transition tab, click the Transition check box, and then click OK
Next select your complete range of cells, E2:E1063
Next select Find & Replace and replace the double quote with nothing.
DO NOT PRESS ENTER
All your fractions should be clear of the double quote marks now.
Next select Copy,
then select a new column that has NOT been formatted it should be just the default General format
Then do a Paste > Special > Values
All your fractions should be in the correct format, at least mine where when I tried it.
Next, go back to the Tool Bar and uncheck the Transition option.
See if it works for you.
Edit Added:
Spoke too soon, the simple fractions did not convert, so it's only a partial solution.
Last edited by xenixman; 01-21-2015 at 11:27 PM.
in 2007 up
maybe
=IFERROR(("0 "&SUBSTITUTE(E2,CHAR(34),""))+0,--TRIM(LEFT(E2,SEARCH("""",E2)-1)))
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
this worked!!!
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks