Extract Number after "="

[Sufiyan97]

I have an issue with below formula to extract number after "=" something is not right with row 28

March Bills.xlsx
	G	H	I
1	Total	Date	Exracted Number
2	40+8+11+1+1=61✔	01-03-2021	61
3	36+10+3=49✔	02-03-2021	49
4	30+27+3+5=65✔	03-03-2021	65
5	3+39+12+1+10+1=66✔	04-03-2021	66
6	39+1+3+3+3+3=52✔	05-03-2021	52
7	3+36+3+20+2+2+2=68✔	06-03-2021	68
8	35+10+4=49✔	07-03-2021	49
9	3+38+24+4+5+1=75✔	08-03-2021	75
10	8+33+12+3+2+12=70✔	09-03-2021	70
11	1+40+24+2=67✔	10-03-2021	67
12	40+14+5+4+1=62✔	11-03-2021	62
13	5+36+3+1+3+1+1+5=55✔	12-03-2021	55
14	38+3+15+2+4=66✔	13-03-2021	66
15	21+3+37+16+3=80✔	14-03-2021	80
16	36+15+6+1=58✔	15-03-2021	58
17	38+6+1=45✔	16-03-2021	45
18	40+26+7+1=74✔	17-03-2021	74
19	49+19+1+1=70✔	18-03-2021	70
20	43+15+1+3+1=63✔	19-03-2021	63
21	20+6+33+13+1+17+4+1=95✔	20-03-2021	95
22	38+33+5+3+30+3+9=121✔	21-03-2021	121
23	5+36+16+3+3=66✔	22-03-2021	66
24	39+26+3+4=72✔	23-03-2021	72
25	44+27+5+2+5=83✔	24-03-2021	83
26	45+12+2+2=61✔	25-03-2021	61
27	43+3+22+1+1+2=72✔	26-03-2021	72
28	40+23+5+5=73✔ ghate che 1	27-03-2021	73 ghate
29	2+35+3+12+3=55✔	28-03-2021	55
30	32+15+1+3=51✔	29-03-2021	51
31	3+43+13+2+2+1+2=66✔	30-03-2021	66
32	41+18+2=61✔	31-03-2021	61
Sheet2

Cell Formulas
Range	Formula
I2:I32	I2	=TRIM(SUBSTITUTE(MID(G2,SEARCH("=",G2)+1,10),"✔",REPT(" ",99)))

 

[jtakw]

Thank you very much Peter_SSs

Can you please explain what's the problem with my formula in row 28

BTW, aside from @Peter_SSs explanation in Post #7...

If you had used the SUBSTITUTE Inside your MID function, rather than Outside, it would have worked:

Your original formula:

=TRIM(SUBSTITUTE(MID(G2,SEARCH("=",G2)+1,10),"✔",REPT(" ",99)))

Should be:

=TRIM(MID(SUBSTITUTE(G2,"✔",REPT(" ",10)),SEARCH("=",G2)+1,10))

 

[Peter_SSs]

Thank you very much Peter for explaining,

No problem.

BTW, just a comment on the formula in post #9 - as it stands, it will fail if rows are subsequently added at the top of the sheet.

 

[Sufiyan97]

No problem.

BTW, just a comment on the formula in post #9 - as it stands, it will fail if rows are subsequently added at the top of the sheet.

After adding 3 rows at the top it gives below result

Book1
	G	H	I
1
2
3
4	Total	Date	Exracted Number
5	40+8+11+1+1=61✔	44256	#N/A
6	36+10+3=49✔	44257	#N/A
7	30+27+3+5=65✔	44258	#N/A
8	3+39+12+1+10+1=66✔	44259	#N/A
9	39+1+3+3+3+3=52✔	44260	#N/A
10	3+36+3+20+2+2+2=68✔	44261	#N/A
11	35+10+4=49✔	44262	#N/A
12	3+38+24+4+5+1=75✔	44263	#N/A
13	8+33+12+3+2+12=70✔	44264	#N/A
14	1+40+24+2=67✔	44265	#N/A
15	40+14+5+4+1=62✔	44266	#N/A
Sheet2

Cell Formulas
Range	Formula
I5:I15	I5	=-LOOKUP(,-LEFT(MID(G5,FIND("=",G5)+1,9),ROW($4:12)))

 

[Peter_SSs]

After adding 3 rows at the top it gives below result

Exactly, that is why I warned about it.

 

[Sufiyan97]

BTW, aside from @Peter_SSs explanation in Post #7...

If you had used the SUBSTITUTE Inside your MID function, rather than Outside, it would have worked:

Your original formula:

=TRIM(SUBSTITUTE(MID(G2,SEARCH("=",G2)+1,10),"✔",REPT(" ",99)))

Should be:

=TRIM(MID(SUBSTITUTE(G2,"✔",REPT(" ",10)),SEARCH("=",G2)+1,10))

Thank you jtakw for correcting me..

 

[Sufiyan97]

Hi,

Just back to fix my formulas in Post #3:

Book3.xlsx
	G	H	I	J
1	Total	Date	Text result	Number result
2	40+8+11+1+1=61✔	3/1/2021	61	61
3	36+10+3=49✔	3/2/2021	49	49
4	30+27+3+5=65✔	3/3/2021	65	65
5	3+39+12+1+10+1=66✔	3/4/2021	66	66
6	39+1+3+3+3+3=52✔	3/5/2021	52	52
7	3+36+3+20+2+2+2=68✔	3/6/2021	68	68
8	35+10+4=49✔	3/7/2021	49	49
9	3+38+24+4+5+1=75✔	3/8/2021	75	75
10	8+33+12+3+2+12=70✔	3/9/2021	70	70
11	1+40+24+2=67✔	3/10/2021	67	67
12	40+14+5+4+1=62✔	3/11/2021	62	62
13	5+36+3+1+3+1+1+5=55✔	3/12/2021	55	55
14	38+3+15+2+4=66✔	3/13/2021	66	66
15	21+3+37+16+3=80✔	3/14/2021	80	80
16	36+15+6+1=58✔	3/15/2021	58	58
17	38+6+1=45✔	3/16/2021	45	45
18	40+26+7+1=74✔	3/17/2021	74	74
19	49+19+1+1=70✔	3/18/2021	70	70
20	43+15+1+3+1=63✔	3/19/2021	63	63
21	20+6+33+13+1+17+4+1=95✔	3/20/2021	95	95
22	38+33+5+3+30+3+9=121✔	3/21/2021	121	121
23	5+36+16+3+3=66✔	3/22/2021	66	66
24	39+26+3+4=72✔	3/23/2021	72	72
25	44+27+5+2+5=83✔	3/24/2021	83	83
26	45+12+2+2=61✔	3/25/2021	61	61
27	43+3+22+1+1+2=72✔	3/26/2021	72	72
28	40+23+5+5=73✔ ghate che 1	3/27/2021	73	73
29	2+35+3+12+3=55✔	3/28/2021	55	55
30	32+15+1+3=51✔	3/29/2021	51	51
31	3+43+13+2+2+1+2=66✔	3/30/2021	66	66
32	41+18+2=61✔	3/31/2021	61	61
Sheet891

Cell Formulas
Range	Formula
I2:I32	I2	=MID(LEFT(G2,FIND("✔",G2)-1),FIND("=",G2)+1,99)
J2:J32	J2	=MID(LEFT(G2,FIND("✔",G2)-1),FIND("=",G2)+1,99)+0

Thank you jtakw for fixing it..