• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1155
  • Last Modified:

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.
  • 2
1 Solution
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

you can extract the number like this


cheers, teylyn
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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)


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


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

=Trim(Left(A1, Find("-", A1&"-") -1))
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now