# Extract Number after "="

#### Sufiyan97

##### Well-known Member
I have an issue with below formula to extract number after "=" something is not right with row 28

March Bills.xlsx
GHI
1TotalDateExracted Number
240+8+11+1+1=61✔01-03-202161
336+10+3=49✔02-03-202149
430+27+3+5=65✔03-03-202165
53+39+12+1+10+1=66✔04-03-202166
639+1+3+3+3+3=52✔05-03-202152
73+36+3+20+2+2+2=68✔06-03-202168
835+10+4=49✔07-03-202149
93+38+24+4+5+1=75✔08-03-202175
108+33+12+3+2+12=70✔09-03-202170
111+40+24+2=67✔10-03-202167
1240+14+5+4+1=62✔11-03-202162
135+36+3+1+3+1+1+5=55✔12-03-202155
1438+3+15+2+4=66✔13-03-202166
1521+3+37+16+3=80✔14-03-202180
1636+15+6+1=58✔15-03-202158
1738+6+1=45✔16-03-202145
1840+26+7+1=74✔17-03-202174
1949+19+1+1=70✔18-03-202170
2043+15+1+3+1=63✔19-03-202163
2120+6+33+13+1+17+4+1=95✔20-03-202195
2238+33+5+3+30+3+9=121✔21-03-2021121
235+36+16+3+3=66✔22-03-202166
2439+26+3+4=72✔23-03-202172
2544+27+5+2+5=83✔24-03-202183
2645+12+2+2=61✔25-03-202161
2743+3+22+1+1+2=72✔26-03-202172
2840+23+5+5=73✔ ghate che 127-03-202173 ghate
292+35+3+12+3=55✔28-03-202155
3032+15+1+3=51✔29-03-202151
313+43+13+2+2+1+2=66✔30-03-202166
3241+18+2=61✔31-03-202161
Sheet2
Cell Formulas
RangeFormula
I2:I32I2=TRIM(SUBSTITUTE(MID(G2,SEARCH("=",G2)+1,10),"✔",REPT(" ",99)))

#### jtakw

##### Well-known Member
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:

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

Should be:

Excel Formula:
``=TRIM(MID(SUBSTITUTE(G2,"✔",REPT(" ",10)),SEARCH("=",G2)+1,10))``

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Well-known Member
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
GHI
1
2
3
4TotalDateExracted Number
540+8+11+1+1=61✔44256#N/A
636+10+3=49✔44257#N/A
730+27+3+5=65✔44258#N/A
83+39+12+1+10+1=66✔44259#N/A
939+1+3+3+3+3=52✔44260#N/A
103+36+3+20+2+2+2=68✔44261#N/A
1135+10+4=49✔44262#N/A
123+38+24+4+5+1=75✔44263#N/A
138+33+12+3+2+12=70✔44264#N/A
141+40+24+2=67✔44265#N/A
1540+14+5+4+1=62✔44266#N/A
Sheet2
Cell Formulas
RangeFormula
I5:I15I5=-LOOKUP(,-LEFT(MID(G5,FIND("=",G5)+1,9),ROW(\$4:12)))

#### Peter_SSs

##### MrExcel MVP, Moderator
After adding 3 rows at the top it gives below result
Exactly, that is why I warned about it.

#### Sufiyan97

##### Well-known Member
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:

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

Should be:

Excel Formula:
``=TRIM(MID(SUBSTITUTE(G2,"✔",REPT(" ",10)),SEARCH("=",G2)+1,10))``
Thank you jtakw for correcting me..

#### Sufiyan97

##### Well-known Member
Hi,

Just back to fix my formulas in Post #3:

Book3.xlsx
GHIJ
1TotalDateText resultNumber result
240+8+11+1+1=61✔3/1/20216161
336+10+3=49✔3/2/20214949
430+27+3+5=65✔3/3/20216565
53+39+12+1+10+1=66✔3/4/20216666
639+1+3+3+3+3=52✔3/5/20215252
73+36+3+20+2+2+2=68✔3/6/20216868
835+10+4=49✔3/7/20214949
93+38+24+4+5+1=75✔3/8/20217575
108+33+12+3+2+12=70✔3/9/20217070
111+40+24+2=67✔3/10/20216767
1240+14+5+4+1=62✔3/11/20216262
135+36+3+1+3+1+1+5=55✔3/12/20215555
1438+3+15+2+4=66✔3/13/20216666
1521+3+37+16+3=80✔3/14/20218080
1636+15+6+1=58✔3/15/20215858
1738+6+1=45✔3/16/20214545
1840+26+7+1=74✔3/17/20217474
1949+19+1+1=70✔3/18/20217070
2043+15+1+3+1=63✔3/19/20216363
2120+6+33+13+1+17+4+1=95✔3/20/20219595
2238+33+5+3+30+3+9=121✔3/21/2021121121
235+36+16+3+3=66✔3/22/20216666
2439+26+3+4=72✔3/23/20217272
2544+27+5+2+5=83✔3/24/20218383
2645+12+2+2=61✔3/25/20216161
2743+3+22+1+1+2=72✔3/26/20217272
2840+23+5+5=73✔ ghate che 13/27/20217373
292+35+3+12+3=55✔3/28/20215555
3032+15+1+3=51✔3/29/20215151
313+43+13+2+2+1+2=66✔3/30/20216666
3241+18+2=61✔3/31/20216161
Sheet891
Cell Formulas
RangeFormula
I2:I32I2=MID(LEFT(G2,FIND("✔",G2)-1),FIND("=",G2)+1,99)
J2:J32J2=MID(LEFT(G2,FIND("✔",G2)-1),FIND("=",G2)+1,99)+0
Thank you jtakw for fixing it..

Replies
4
Views
440
Replies
0
Views
76
Replies
2
Views
445
Replies
4
Views
72
Replies
2
Views
63

1,148,006
Messages
5,744,311
Members
423,859
Latest member
joannetung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back