Fraction stored as text

[SQUIDD]

Hi all

I have been looking around on this issue.
Cannot seem to find a way forward.

I have fractions stored as text like 11 1/4.

When i use
=left(a1,1) is 1
=left(a1,2) is 11
=left(a1,3) is 1/4

is there a formula i can use to sort this out to output as 11.25?

Or pehaps UDF?

thanks all

please see image of how they are displayed

Dave

 

[AlanY]

have a look of this

How to convert between fraction and decimal in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

 

[SQUIDD]

have a look of this

How to convert between fraction and decimal in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

Hi Alan

Thanks for looking, but im afraid i have already tried to format these.

it would appear whatever i try to format to, does not change the outpot in the cell

dave

 

[AlphaFrog]

If there is a space that separates the whole number from the fraction, then try this...

=IF(ISERROR(SEARCH(" ",A1)),LEFT(A1,SEARCH("/",A1)-1)/MID(A1,SEARCH("/",A1)+1,99),LEFT(A1,SEARCH(" ",A1)-1)+MID(A1,SEARCH("",A1)+1,SEARCH("/",A1)-1-SEARCH(" ",A1))/MID(A1,SEARCH("/",A1)+1,99))

Alternatively, select the column and use the Replace function (Ctrl+H) and replace a space character with a space character. Excel will then coerce the text into numbers. You may then change the cell formatting of the column from fraction to decimal as AlanY suggested.

 

[SQUIDD]

Hi Aplha frog

there is not a space between unfortunatly. Thanks for trying.
Basicall the cell for the 11 and 1/4 is
1st charracter is a 1
2nd characher is a 1
3rd charracter is 1/4(but actually the 1 is over the 4 as per the image.

Dave

 

[ZVI]

Hi,
Try this:

Wb1
	A	B
1	Text	Number
2	11¼	11.25
3	2½	2.5
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=--SUBSTITUTE(SUBSTITUTE(A2,"¼"," 1/4"),"½"," 1/2")

 

[SQUIDD]

Hi ZVI

You nailed it mate, worked perfectly. Thank you.

nice job and thanks everyone else that helped previously.

Dave

 

[SQUIDD]

ZVI

How can i also cover a 3/4" scenario.

thanks

dave

 

[SQUIDD]

no worries

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I3,"¼"," 1/4"),"½"," 1/2"),"¾"," 3/4")

thanks

dave

 

[ZVI]

Glad you've got it working, Dave!