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)))

 

[Peter_SSs]

Try this instead

=REPLACE(LEFT(G2,FIND("✔",G2)-1),1,FIND("=",G2),"")+0

If you are happy to have that number returned as text you can remove the "+0" at the end.

 

[jtakw]

Hi,

Are your strings Always 2 characters?

Try these:

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	21	21
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	=RIGHT(REPLACE(G2,FIND("✔",G2),99,""),2)
J2:J32	J2	=RIGHT(REPLACE(G2,FIND("✔",G2),99,""),2)+0

 

[Sufiyan97]

Thank you very much Peter_SSs

Works Perfect

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

 

[Sufiyan97]

Hi,

Are your strings Always 2 characters?

Try these:

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	21	21
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	=RIGHT(REPLACE(G2,FIND("✔",G2),99,""),2)
J2:J32	J2	=RIGHT(REPLACE(G2,FIND("✔",G2),99,""),2)+0

Thank you jtakw for reply
as you can see some times there is 3 characters like in row 22

 

[jtakw]

Sorry, missed row 22, 3 characters.

 

[Peter_SSs]

Thank you very much @Peter_SSs

Works Perfect

You're welcome. Glad it worked for you.

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

Yes.
Your formula finds the "=" sign and then takes the next 10 characters. In row 28 that part would return "73✔ ghate "
It then replaces the ✔ with 99 spaces^{(see note below)} producing a string like this (where . represents a space) only with more spaces: "73....................ghate."
Trim then removes the space at the right and reduces the internal space to one, giving "73 ghate"

Note: Replacing the ✔ with 99 spaces achieves nothing as those spaces are then immediately removed by the TRIM() function

 

[Sufiyan97]

You're welcome. Glad it worked for you.


Yes.
Your formula finds the "=" sign and then takes the next 10 characters. In row 28 that part would return "73✔ ghate "
It then replaces the ✔ with 99 spaces^{(see note below)} producing a string like this (where . represents a space) only with more spaces: "73....................ghate."
Trim then removes the space at the right and reduces the internal space to one, giving "73 ghate"

Note: Replacing the ✔ with 99 spaces achieves nothing as those spaces are then immediately removed by the TRIM() function

Thank you very much Peter for explaining,

Now I got your point.

 

[shaowu459]

工作簿1
	G	H	I
1	Total	Date	Exracted Number
2	40+8+11+1+1=61✔	2021/3/1	61
3	36+10+3=49✔	2021/3/2	49
4	30+27+3+5=65✔	2021/3/3	65
5	3+39+12+1+10+1=66✔	2021/3/4	66
6	39+1+3+3+3+3=52✔	2021/3/5	52
7	3+36+3+20+2+2+2=68✔	2021/3/6	68
8	35+10+4=49✔	2021/3/7	49
9	3+38+24+4+5+1=75✔	2021/3/8	75
10	8+33+12+3+2+12=70✔	2021/3/9	70
11	1+40+24+2=67✔	2021/3/10	67
12	40+14+5+4+1=62✔	2021/3/11	62
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=-LOOKUP(,-LEFT(MID(G2,FIND("=",G2)+1,9),ROW($1:9)))

 

[jtakw]

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