Taking multiple values from one cell and rating them in one cell with a 1 - 10 point scale system.

[Andu]

I'm wondering if there's any way to put multiple different values into one cell and separating them with some kind of symbol, for example "\" like seen on the picture and then reading those values to another cell, rating them individually from 1 - 10 and then displaying them in one cell and separating with the same symbol as used before. The function I use for rating is (example for cell C2): =1+9*(B2-MIN(B$2:B$8))/(MAX(B$2:B$8)-MIN(B$2:B$8)) which basically rates the minimum value as 1 and the maximum as 10 and rest of the values anywhere from 1 - 10. The first picture is what I have now and the second picture is what I'd like to have.

 

[Vogateer]

For separating them, you could use the search feature.
Fore example, assuming 54\11 is in B2, you can use this to get just the 54:

=LEFT(B2,SEARCH("\", B2)-1)

and use this to get the 11 from B2:

=MID(B2, SEARCH("\",B2)+1, LEN(B2)-SEARCH("\", B2))

You could put that part of the formula in some other formula.

 

[Andu]

For separating them, you could use the search feature.
Fore example, assuming 54\11 is in B2, you can use this to get just the 54:

=LEFT(B2,SEARCH("\", B2)-1)

and use this to get the 11 from B2:

=MID(B2, SEARCH("\",B2)+1, LEN(B2)-SEARCH("\", B2))

You could put that part of the formula in some other formula.

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm wondering if there's any way to put multiple different values into one cell and separating them with some kind of symbol, for example "\" like seen on the picture and then reading those values to another cell, rating them individually from 1 - 10 and then displaying them in one cell and separating with the same symbol as used before.

Like this?

Andu.xlsm
	B	C
1
2	54/11	7.08823529411765/10
3	31/4	1/1
4	31/4	1/1
5	44/6	4.44117647058824/3.57142857142857
6	31/4	1/1
7	65/9	10/7.42857142857143
8	65/9	10/7.42857142857143
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,(1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L)))&"/"&(1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))))

 

[Andu]

Welcome to the MrExcel board!


Like this?

Andu.xlsm
	B	C
1
2	54/11	7.08823529411765/10
3	31/4	1/1
4	31/4	1/1
5	44/6	4.44117647058824/3.57142857142857
6	31/4	1/1
7	65/9	10/7.42857142857143
8	65/9	10/7.42857142857143
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,(1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L)))&"/"&(1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))))

Yes! Thank you very much. Is there a way to have it display the rating with up to max of 3 decimal places (excluding whole numbers)?

 

[Peter_SSs]

Is there a way to have it display the rating with up to max of 3 decimal places (excluding whole numbers)?

Sure

Andu.xlsm
	B	C
1
2	54/11	7.088/10
3	31/4	1/1
4	31/4	1/1
5	44/6	4.441/3.571
6	31/4	1/1
7	65/9	10/7.429
8	65/9	10/7.429
Sheet1.1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,ROUND((1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L))),3)&"/"&ROUND((1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))),3))

 

[Andu]

Sure

Andu.xlsm
	B	C
1
2	54/11	7.088/10
3	31/4	1/1
4	31/4	1/1
5	44/6	4.441/3.571
6	31/4	1/1
7	65/9	10/7.429
8	65/9	10/7.429
Sheet1.1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(F,FIND("/",B$2:B$8),L,TEXTSPLIT(TEXTJOIN(" ",,LEFT(B$2:B$8,F-1))," ")+0,R,TEXTSPLIT(TEXTJOIN(" ",,REPLACE(B$2:B$8,1,F,""))," ")+0,ROUND((1+9*(LEFT(B2,FIND("/",B2)-1)-MIN(L))/(MAX(L)-MIN(L))),3)&"/"&ROUND((1+9*(REPLACE(B2,1,FIND("/",B2),"")-MIN(R))/(MAX(R)-MIN(R))),3))

Perfect! Appreciate it!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.