Conversion help

[Iriemon]

I have data as shown below.

7/32" 11/32"
1/4" 3/8"
5/16" 7/16"
3/8" 1/2"


I have changed the formatting for all cells to "numbers, 3 place decimals,
then "copy,paste special, values".

My problem is that when I do a find and replace (Ctrl-H) to golbally replace
the quote marks with nothing, I get data like this:

OLd Values: 7/32" 11/32"
New values: 11871.000 11994.000

But if I go into each cell and delete the quote mark manually, it converts
to the proper decimal equivalent.

Unfortunately, I have several hundred of these to convert. Anyone know how
to solve this?

Thanks

Iriemon

 

[OssieMac]

Hi Iriemon,

Your new values 11871.000 and 11994.000 are actually dates. Jul 1932 and Nov
1932. Dates can be entered with just 2 parameters separated by / and
depending on the regional date format and what the numbers are whether they
become day and month, month and day or month and year.

I am assuming that the fractions have been input as text and not numbers
formatted as fractions otherwise they would convert to numeric equivalent by
simply changing to number format.

Anyway to solve your problem you can insert a helper column and insert the
following formula to break up the text fraction into its separate components
and calculate the decimal equivalent. The formula assumes that the fraction
is in cell A2. Simply replace A2 with your first cell and then copy the
formula down. You can then select the entire column and Copy then Paste
Special -> Values over top of itself.

Note: the formula is one line although it breaks in this post.

=LEFT(LEFT(A2,LEN(A2)-1),FIND("/",LEFT(A2,LEN(A2)-1))-1)/MID(LEFT(A2,LEN(A2)-1),FIND("/",LEFT(A2,LEN(A2)-1))+1,255)