Need to show remove * and add **

[sksanjeev786]

Hi Team

Need help to remove only "*" from the number and where ever we have "**" need to show as ** only
For example: AS13 = I need as only 39 and AT13 need as only "**" (No number for that criteria )

FS.xlsx
	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
9	100	100 **	100 **	100 *	100 **	91 **	99 v	99 v	98	100	98	97	91 *
10	85	74 **	100 **	86 *	80 **	73 **	77 v	75	71	78	73	76	64 *
11	57	63 **	82 **	64 *	65 **	73 **	52	52	50	53 y	47	44	42 *
12	54	41 **	82 **	47 *	50 **	73 **	39	40	41	45	40	36	40 *
13	39 *	41 **	86 **	32 **	50 **	70 **	36	39	43 t	37	36	42	50 **
14	21	19 **	36 **	8 *	10 **	55 **	15	16	20 t	15 y	13	9	18 *
FS

Cell Formulas
Range	Formula
AS9:BE14	AS9	=IF(AS$1=$C$1,INDEX(INDIRECT("'"&$A$2&"'!A"&$B$7&":IV"&$C$7&""),MATCH($D9,INDIRECT("'"&$A$2&"'!A"&$B$7&":A"&$C$7&""),0)+1,AS$2),INDEX(INDIRECT("'"&$A$2&"'!A"&$B$6&":IV"&$C$6&""),MATCH($D9,INDIRECT("'"&$A$2&"'!A"&$B$6&":A"&$C$6&""),0)+1,AS$2))

Thanks in advance
Sanjeev

 

[Phuoc]

Try this:

=IF(RIGHT(OldFormula,2)="**","**",IF(RIGHT(OldFormula,1)="*",SUBSTITUTE(OldFormula,"*","")))

 

[sksanjeev786]

Try this:

=IF(RIGHT(OldFormula,2)="**","**",IF(RIGHT(OldFormula,1)="*",SUBSTITUTE(OldFormula,"*","")))

Work well

Thank you so much

i have 2 more scenarios in my data file added below I need output as G column

FS_.xlsx
	C	D	E	F	G
46	33*		33		33
47	33**		**		**
48	-*		FALSE		-
49	-		FALSE		-
Mixture FS

Cell Formulas
Range	Formula
E46:E49	E46	=IF(RIGHT(C46,2)="**","**",IF(RIGHT(C46,1)="*",SUBSTITUTE(C46,"*","")))

Thanks in advance

 

[Phuoc]

Like this:

=IF(RIGHT(C46,2)="**","**",SUBSTITUTE(C46,"*",""))

 

[Gokhan Aycan]

What if it's just a number, say, AS9? It would show as FALSE.

=IF(RIGHT(OldFormula,2)="**","**",IF(RIGHT(OldFormula,1)="*",SUBSTITUTE(OldFormula,"*",""),OldFormula))

Should catch those as well. Another observation (maybe ) is it seems you have a space in between numbers and asterisks.

 

[sksanjeev786]

Like this:

=IF(RIGHT(C46,2)="**","**",SUBSTITUTE(C46,"*",""))

Perfect!!!!

Thank you so much for your help Phuoc

 

[sksanjeev786]

What if it's just a number, say, AS9? It would show as FALSE.

=IF(RIGHT(OldFormula,2)="**","**",IF(RIGHT(OldFormula,1)="*",SUBSTITUTE(OldFormula,"*",""),OldFormula))

Should catch those as well. Another observation (maybe ) is it seems you have a space in between numbers and asterisks.

thank you so much for your observation Aycan

We may have that scenario for other data files

so definitely it is going to help us

Thank you so much for your time on this