Trim Class , Distance , Stakes .

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
Hello all ,

I am wanting to get some functions that can give me the results I have in cols B C and D

The asterix * must also appear in the results if mentioned in col A .

Many thanks .
Excel Workbook
ABCD
1Col AClassDistStakes
2WFA 1600m - $200,000WFA1600200000
3OPN HCP 2000m - $40,000OPN HCP200040000
4Rating 85 Benchmark 1400m - $8,000Rating 85 Benchmark14008000
5Rating 65 Benchmark* 2200m - $30,000Rating 65 Benchmark*220030000
6SP COND 1300m - $150,000SP COND1300150000
73YOF SW 1200m - $70,0003YOF SW120070000
8MDN 1400m - $30,000MDN140030000
9OPN HCP 2500m - $2,500,000OPN HCP25002500000
10Rating 65 Benchmark Fillies and Mares* 1400m - $7,000Rating 65 Benchmark Fillies and Mares*14007000
Sheet1
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Try


Excel 2010
ABCD
2WFA 1600m - $200,000WFA1600200000
3OPN HCP 2000m - $40,000OPN HCP200040000
4Rating 85 Benchmark 1400m - $8,000Rating 85 Benchmark14008000
5Rating 65 Benchmark* 2200m - $30,000Rating 65 Benchmark*220030000
6SP COND 1300m - $150,000SP COND1300150000
73YOF SW 1200m - $70,0003YOF SW120070000
8MDN 1400m - $30,000MDN140030000
9OPN HCP 2500m - $2,500,000OPN HCP25002500000
10Rating 65 Benchmark Fillies and Mares* 1400m - $7,000Rating 65 Benchmark Fillies and Mares*14007000
Sheet3
Cell Formulas
RangeFormula
B2=LEFT(A2,FIND(C2&"m",A2)-2)
C2=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),FIND("m ",SUBSTITUTE(A2," ",REPT(" ",99)))-99,99))+0
D2=SUBSTITUTE(MID(A2,FIND("m - ",A2)+3,99),"$","")+0
 

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
Perfect again Momentman , 100% .
Exactly what i needed .
Thanks .
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,649
Messages
5,838,560
Members
430,555
Latest member
Schturman

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