Solved

# Using substring in excel 2007 to pull out numbers

Posted on 2011-03-02
1,135 Views
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.
0
Question by:morinia
• 2

LVL 50

Expert Comment

Hello,

you can extract the number like this

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

cheers, teylyn
0

LVL 50

Accepted Solution

teylyn earned 250 total points
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

LVL 22

Expert Comment

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

=Trim(Left(A1, Find("-", A1&"-") -1))
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…