Extract Number after "="

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
529
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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:

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
Joined
May 28, 2005
Messages
51,780
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 12, 2019
Messages
529
Office Version
  1. 2013
Platform
  1. Windows
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)))
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
529
Office Version
  1. 2013
Platform
  1. Windows
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:

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
Joined
Apr 12, 2019
Messages
529
Office Version
  1. 2013
Platform
  1. Windows
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..
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top