Convert fraction to text within a text string

[deanv2]

Hello,
How can I convert a fraction to text within a test string. for example: Screw, ¼-20 X 1½", Phillips, Flat, MS, Zinc. I need the 1/4 and the 11/2 fractions converted to text. Final result would look like this: Screw, 1/4-20 X 1 1/2" Phillips, Flat, MS, Zinc. I am unable to upload the spreadsheet due to the embedded fractions
Thanks you for your help!

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B
1	Screw, ¼-20 X 1½", Phillips, Flat, MS, Zinc.	Screw, 1/4-20 X 1 1/2", Phillips, Flat, MS, Zinc.
Sheet905

Cell Formulas
Range	Formula
B1	B1	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(188)," 1/4"),CHAR(189)," 1/2"),CHAR(190)," 3/4"))

 

[kweaver]

Not necessarily all inclusive, but this might start:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(188),"1/4"),CHAR(189)," 1/2"),CHAR(190),"3/4")

I don't think the CHAR codes include 1/8 if you needed that. SIGH.

 

[deanv2]

Hi,

Try this:

Book3.xlsx
	A	B
1	Screw, ¼-20 X 1½", Phillips, Flat, MS, Zinc.	Screw, 1/4-20 X 1 1/2", Phillips, Flat, MS, Zinc.
Sheet905

Cell Formulas
Range	Formula
B1	B1	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(188)," 1/4"),CHAR(189)," 1/2"),CHAR(190)," 3/4"))

Thank you for the quick reply and solution

 

[jtakw]

You're welcome, thanks for the feedback.