Quick way to convert fraction to decimal

[cccbzg]

Hi,

I frequently get data in the format below. I need it to be a fraction. What I usually do is use "text to columns" and work from there. Is there an easier way?

Thanks in advance!
Bonnie

37 / 78

52 / 78

50 / 78

48 / 78

20 / 78

47 / 78

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cccbzg]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done! Thanks for the advice.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B
1
2	37 / 78	0.474359
3	52 / 78	0.666667
4	50 / 78	0.641026
5	48 / 78	0.615385
6	20 / 78	0.25641
7	47 / 78	0.602564
Main

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTBEFORE(A2,"/")/TEXTAFTER(A2,"/")

 

[Dave Patton]

The results are formatted as fraction.
The second solution uses Evaluate which can work with + - ^ etc.

Lambda DGP.xlsm
	A	B	C
22	37 / 78	1/2	1/2
23	52 / 78	2/3	2/3
24	50 / 78	2/3	2/3
25	48 / 78	5/8	5/8
26	20 / 78	1/4	1/4
27	47 / 78	3/5	3/5
1g

Cell Formulas
Range	Formula
B22:B27	B22	=TEXTBEFORE(A22," / ")/TEXTAFTER(A22," / ")
C22:C27	C22	=EvalCell(A22)

 

[GSP75]

Yes, there is an easier way to convert the data to fractions without using the "text to columns" feature.

You can use the SUBSTITUTE function to replace the space between the numbers with a division symbol ("/"), and then use the VALUE function to convert the resulting text string to a number. For example, assuming the first data point is in cell A1, you can use the following formula in cell B1 to convert it to a fraction:

=VALUE(SUBSTITUTE(A1," ","/"))

This formula first replaces the space character with a division symbol using the SUBSTITUTE function, and then converts the resulting text string to a number using the VALUE function. You can then copy this formula down to convert all the data points to fractions.

This method is much faster and more efficient than using "text to columns" and can handle large amounts of data with ease.

 

[awoohaw]

Yes, there is an easier way to convert the data to fractions without using the "text to columns" feature.

You can use the SUBSTITUTE function to replace the space between the numbers with a division symbol ("/"), and then use the VALUE function to convert the resulting text string to a number. For example, assuming the first data point is in cell A1, you can use the following formula in cell B1 to convert it to a fraction:

=VALUE(SUBSTITUTE(A1," ","/"))

This formula first replaces the space character with a division symbol using the SUBSTITUTE function, and then converts the resulting text string to a number using the VALUE function. You can then copy this formula down to convert all the data points to fractions.

This method is much faster and more efficient than using "text to columns" and can handle large amounts of data with ease.

@GSP75 , don't understand how the substitute formula as you use above resolves to only one slash? by substituting " " with a / you will get ///, wont you?

 

[awoohaw]

The results are formatted as fraction.
The second solution uses Evaluate which can work with + - ^ etc.

Lambda DGP.xlsm
	A	B	C
22	37 / 78	1/2	1/2
23	52 / 78	2/3	2/3
24	50 / 78	2/3	2/3
25	48 / 78	5/8	5/8
26	20 / 78	1/4	1/4
27	47 / 78	3/5	3/5
1g

Cell Formulas
Range	Formula
B22:B27	B22	=TEXTBEFORE(A22," / ")/TEXTAFTER(A22," / ")
C22:C27	C22	=EvalCell(A22)

@Dave Patton , I don't understand how the fraction format is used in this situation. The value in A22 does not resolve to one-half?

 

[Dave Patton]

The question included "I need it to be a fraction" .
Format the information per your preference.

Lambda DGP.xlsm
	A	B	C	D	E
20
21		Decimal		Fraction as sixteenth
22	37 / 78	47.44%	47.44%	8/16	8/16
23	52 / 78	66.67%	66.67%	11/16	11/16
24	50 / 78	64.10%	64.10%	10/16	10/16
25	48 / 78	61.54%	61.54%	10/16	10/16
26	20 / 78	25.64%	25.64%	4/16	4/16
27	47 / 78	60.26%	60.26%	10/16	10/16
28	5 / 10	0.5	50.00%	8/16	8/16
29
1g

Cell Formulas
Range	Formula
B22:B28	B22	=TEXTBEFORE(A22," / ")/TEXTAFTER(A22," / ")
C22:C28	C22	=EvalCell(A22)
D22:D28	D22	=TEXTBEFORE(A22," / ")/TEXTAFTER(A22," / ")
E22:E28	E22	=EvalCell(A22)

 

[cccbzg]

Thanks for that.
How about

Fluff.xlsm
	A	B
1
2	37 / 78	0.474359
3	52 / 78	0.666667
4	50 / 78	0.641026
5	48 / 78	0.615385
6	20 / 78	0.25641
7	47 / 78	0.602564
Main

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTBEFORE(A2,"/")/TEXTAFTER(A2,"/")

This worked great!! Thanks so much!!