Using substring in excel 2007 to pull out numbers

I am using sumifs to add data on a spreadsheet.  The field that I would like to compare is concatanated as follows:

  01 - Red
  02 - Yellow
  03 - Blue
  04 - Green

I would like to compare just to the numeric part of the string when reading the spreadsheet.  What is the substring statement I would use to isolate the numbers.  The string is indented so the numbers are not in columns 1 and 2. It appears there are possibly 2 spaces in front of the numbers.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Please find attached a sample with a Sumproduct that counts the occurrences of the numbers in the range.

One option is if the comparison with true numbers (in D2)

=SUMPRODUCT(--(LEFT($A$2:$A$11,FIND("-",$A$2:$A$11)-2)+0=D2))


Another option is the comparison with text, like 01, 02, 03 (in G2)

=SUMPRODUCT(--(TRIM(LEFT($A$2:$A$11,FIND("-",$A$2:$A$11)-1))=G2))

cheers, teylyn
Book3.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you can extract the number like this

=LEFT(A1,FIND("-",A1&"-")-1)+0

cheers, teylyn
0
 
rspahitzCommented:
If you want to retain the leading zeros, you'll need to take a similar approach:

=Trim(Left(A1, Find("-", A1&"-") -1))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.