need to add * if score are less then 50

[sksanjeev786]

Hi Team,

I need to add "*" if the score is between 30-50 in the below data and less than 30

(49)* and (26)**

HUMIRA- Bases: Jun (70), Jul (73), Aug (71), Sep (67), Oct (78), Nov (78), Dec (67), Jan (69), Feb (72), Mar (64), Apr (55), May (64), June (70)
Skyrizi- Bases: Jun (-), Jul (-), Aug (-), Sep (-), Oct (-), Nov (-), Dec (26), Jan (53), Feb (55), Mar (49), Apr (43), May (49), June (57)
Taltz- Bases: Jun (60), Jul (63), Aug (54), Sep (52), Oct (66), Nov (65), Dec (49), Jan (51), Feb (57), Mar (48), Apr (45), May (47), June (47)
Tremfya - Bases: Jun (60), Jul (62), Aug (56), Sep (54), Oct (68), Nov (64), Dec (52), Jan (52), Feb (57), Mar (51), Apr (45), May (51), June (54)
Cosentyx- Bases: Jun (62), Jul (60), Aug (52), Sep (53), Oct (70), Nov (69), Dec (53), Jan (55), Feb (62), Mar (57), Apr (49), May (48), June (55)
Enbrel- Bases: Jun (62), Jul (64), Aug (56), Sep (56), Oct (73), Nov (72), Dec (57), Jan (57), Feb (61), Mar (50), Apr (42), May (55), June (60)
Otezla- Bases: Jun (62), Jul (65), Aug (58), Sep (56), Oct (70), Nov (65), Dec (57), Jan (60), Feb (61), Mar (48), Apr (42), May (57), June (57)

Regards
Sanjeev

 

[JamesCanale]

365 only.

MrExcelPlayground10.xlsx
	A
1	HUMIRA- Bases: Jun (70), Jul (73), Aug (71), Sep (67), Oct (78), Nov (78), Dec (67), Jan (69), Feb (72), Mar (64), Apr (55), May (64), June (70)
2	Skyrizi- Bases: Jun (-), Jul (-), Aug (-), Sep (-), Oct (-), Nov (-), Dec (26), Jan (53), Feb (55), Mar (49), Apr (43), May (49), June (57)
3	Taltz- Bases: Jun (60), Jul (63), Aug (54), Sep (52), Oct (66), Nov (65), Dec (49), Jan (51), Feb (57), Mar (48), Apr (45), May (47), June (47)
4	Tremfya - Bases: Jun (60), Jul (62), Aug (56), Sep (54), Oct (68), Nov (64), Dec (52), Jan (52), Feb (57), Mar (51), Apr (45), May (51), June (54)
5	Cosentyx- Bases: Jun (62), Jul (60), Aug (52), Sep (53), Oct (70), Nov (69), Dec (53), Jan (55), Feb (62), Mar (57), Apr (49), May (48), June (55)
6	Enbrel- Bases: Jun (62), Jul (64), Aug (56), Sep (56), Oct (73), Nov (72), Dec (57), Jan (57), Feb (61), Mar (50), Apr (42), May (55), June (60)
7	Otezla- Bases: Jun (62), Jul (65), Aug (58), Sep (56), Oct (70), Nov (65), Dec (57), Jan (60), Feb (61), Mar (48), Apr (42), May (57), June (57)
8
9	HUMIRA- Bases: Jun (70), Jul (73), Aug (71), Sep (67), Oct (78), Nov (78), Dec (67), Jan (69), Feb (72), Mar (64), Apr (55), May (64), June (70)
10	Skyrizi- Bases: Jun (-), Jul (-), Aug (-), Sep (-), Oct (-), Nov (-), Dec (26)**, Jan (53), Feb (55), Mar (49)*, Apr (43)*, May (49)*, June (57)
11	Taltz- Bases: Jun (60), Jul (63), Aug (54), Sep (52), Oct (66), Nov (65), Dec (49)*, Jan (51), Feb (57), Mar (48)*, Apr (45)*, May (47)*, June (47)*
12	Tremfya - Bases: Jun (60), Jul (62), Aug (56), Sep (54), Oct (68), Nov (64), Dec (52), Jan (52), Feb (57), Mar (51), Apr (45)*, May (51), June (54)
13	Cosentyx- Bases: Jun (62), Jul (60), Aug (52), Sep (53), Oct (70), Nov (69), Dec (53), Jan (55), Feb (62), Mar (57), Apr (49)*, May (48)*, June (55)
14	Enbrel- Bases: Jun (62), Jul (64), Aug (56), Sep (56), Oct (73), Nov (72), Dec (57), Jan (57), Feb (61), Mar (50)*, Apr (42)*, May (55), June (60)
15	Otezla- Bases: Jun (62), Jul (65), Aug (58), Sep (56), Oct (70), Nov (65), Dec (57), Jan (60), Feb (61), Mar (48)*, Apr (42)*, May (57), June (57)
Sheet13

Cell Formulas
Range	Formula
A9:A15	A9	=TEXTJOIN(", ",TRUE,LET(a,FILTERXML("<x><y>"&SUBSTITUTE(A1,",","</y><y>")&"</y></x>","//y"),b,SEARCH("(",a),c,-RIGHT(a,LEN(a)-b+1),IF(ISERROR(c),a,IF(c<30,a&"**",IF(c<=50,a&"*",a)))))

 

[sksanjeev786]

365 only.

MrExcelPlayground10.xlsx
	A
1	HUMIRA- Bases: Jun (70), Jul (73), Aug (71), Sep (67), Oct (78), Nov (78), Dec (67), Jan (69), Feb (72), Mar (64), Apr (55), May (64), June (70)
2	Skyrizi- Bases: Jun (-), Jul (-), Aug (-), Sep (-), Oct (-), Nov (-), Dec (26), Jan (53), Feb (55), Mar (49), Apr (43), May (49), June (57)
3	Taltz- Bases: Jun (60), Jul (63), Aug (54), Sep (52), Oct (66), Nov (65), Dec (49), Jan (51), Feb (57), Mar (48), Apr (45), May (47), June (47)
4	Tremfya - Bases: Jun (60), Jul (62), Aug (56), Sep (54), Oct (68), Nov (64), Dec (52), Jan (52), Feb (57), Mar (51), Apr (45), May (51), June (54)
5	Cosentyx- Bases: Jun (62), Jul (60), Aug (52), Sep (53), Oct (70), Nov (69), Dec (53), Jan (55), Feb (62), Mar (57), Apr (49), May (48), June (55)
6	Enbrel- Bases: Jun (62), Jul (64), Aug (56), Sep (56), Oct (73), Nov (72), Dec (57), Jan (57), Feb (61), Mar (50), Apr (42), May (55), June (60)
7	Otezla- Bases: Jun (62), Jul (65), Aug (58), Sep (56), Oct (70), Nov (65), Dec (57), Jan (60), Feb (61), Mar (48), Apr (42), May (57), June (57)
8
9	HUMIRA- Bases: Jun (70), Jul (73), Aug (71), Sep (67), Oct (78), Nov (78), Dec (67), Jan (69), Feb (72), Mar (64), Apr (55), May (64), June (70)
10	Skyrizi- Bases: Jun (-), Jul (-), Aug (-), Sep (-), Oct (-), Nov (-), Dec (26)**, Jan (53), Feb (55), Mar (49)*, Apr (43)*, May (49)*, June (57)
11	Taltz- Bases: Jun (60), Jul (63), Aug (54), Sep (52), Oct (66), Nov (65), Dec (49)*, Jan (51), Feb (57), Mar (48)*, Apr (45)*, May (47)*, June (47)*
12	Tremfya - Bases: Jun (60), Jul (62), Aug (56), Sep (54), Oct (68), Nov (64), Dec (52), Jan (52), Feb (57), Mar (51), Apr (45)*, May (51), June (54)
13	Cosentyx- Bases: Jun (62), Jul (60), Aug (52), Sep (53), Oct (70), Nov (69), Dec (53), Jan (55), Feb (62), Mar (57), Apr (49)*, May (48)*, June (55)
14	Enbrel- Bases: Jun (62), Jul (64), Aug (56), Sep (56), Oct (73), Nov (72), Dec (57), Jan (57), Feb (61), Mar (50)*, Apr (42)*, May (55), June (60)
15	Otezla- Bases: Jun (62), Jul (65), Aug (58), Sep (56), Oct (70), Nov (65), Dec (57), Jan (60), Feb (61), Mar (48)*, Apr (42)*, May (57), June (57)
Sheet13

Cell Formulas
Range	Formula
A9:A15	A9	=TEXTJOIN(", ",TRUE,LET(a,FILTERXML("<x><y>"&SUBSTITUTE(A1,",","</y><y>")&"</y></x>","//y"),b,SEARCH("(",a),c,-RIGHT(a,LEN(a)-b+1),IF(ISERROR(c),a,IF(c<30,a&"**",IF(c<=50,a&"*",a)))))

Hi James,

Thank you so much it works very well..!!!

If you don't mind could you please elaborate the formula I am very egar to understand they way you have written the formula

 

[JamesCanale]

The best way to see that is to look at all of the portions of the LET function.

So take out the whole textjoin part and have a look at each portion of the text join.

'a' splits the line of text by each ',' (comma) into an array. Soon a TEXTSPLIT function is coming, but I don't have it yet.
'b' looks for the open parenthesis location
'c' takes the whole number portion of each text, so something like (72). Which looks like a negative number to excel, so I put a '-' in front of it to make it the number. In this way, the thing will work with 1 or 2 or 3 digit numbers (or longer too).
Then the last tricky bit, if c is an error, it just returns the 'a'. If c<30, it returns a with "**" at the end, if it's <=50 you get a with "*" at the end.

The textjoin puts it all back together.

 

[sksanjeev786]

The best way to see that is to look at all of the portions of the LET function.

So take out the whole textjoin part and have a look at each portion of the text join.

'a' splits the line of text by each ',' (comma) into an array. Soon a TEXTSPLIT function is coming, but I don't have it yet.
'b' looks for the open parenthesis location
'c' takes the whole number portion of each text, so something like (72). Which looks like a negative number to excel, so I put a '-' in front of it to make it the number. In this way, the thing will work with 1 or 2 or 3 digit numbers (or longer too).
Then the last tricky bit, if c is an error, it just returns the 'a'. If c<30, it returns a with "**" at the end, if it's <=50 you get a with "*" at the end.

The textjoin puts it all back together.

'

Wow!! thank you so much for your time on this.

Really appreciate your hard work with this

Regards,
Sanjeev

 

[JamesCanale]

Thank you for the puzzle.