IF formula

**wglenn1** · 02-07-2011, 01:37 PM

I am trying to set up an excel table to automatically pick up pipe lengths according to the size of pipe baed on just text. Here is what I it looks like:
REQ'D STORM DRAIN PIPE 15" X 82' RT.
and the formula:
=IF(ISNUMBER(FIND("15""",C8)),MID(C8,3… "")

My formula is currently reading 3 characters for the pipe lengths. This is fine for lengths >100', but my problem is when they are <100' I am getting values with the ' symbol, I do not want that to show up. Is there a way to put in some and/or statement where based on the pipe length it picks up it decides on the number of characters to read? Or is there any other way to make the ' symbol just not show up?

any help is appreciated

**ChemistB** · 02-07-2011, 01:50 PM

Based on the information you've supplied, I'd wrap a substitute around your MID formula
=SUBSTITUTE(MID(C8,3,...)&"'","'","") with the ' symbol wrapped in quotes
Does that work for you?

**wglenn1** · 02-07-2011, 02:21 PM

Sorry didn't realize I didn't paste the entire formula. Here is what I am working with:

=IF(ISNUMBER(FIND("15""",C8)),MID(C8,30,3), "")

so are you saying put the substitute in my IF statement before MID?

thanks

**ChemistB** · 02-07-2011, 02:24 PM

=if(isnumber(find("15""",c8)),substitute(mid(c8,30,3)&"'","'",""), "")

**wglenn1** · 02-07-2011, 02:27 PM

Exactly what I needed. thanks alot

**wglenn1** · 02-07-2011, 02:33 PM

One more thing please. I use that formula and get a column of values, but it will not give me anything when I try to use SUM...any ideas?

**ChemistB** · 02-07-2011, 02:35 PM

=if(isnumber(find("15""",c8)),substitute(mid(c8,30,3)&"'","'","")+0, "")
When using the LEFT, MID, RIGHT and Concatinate (&) functions, Excel sees the result as a text string. The +0 converts it back to a number

**wglenn1** · 02-07-2011, 03:33 PM

ok thats awesome. Last question I have.

I have this IF statement here:
=IF(D8="CB-01","1", "")

How do I convert this formula back to a number so I can sum up these columns?

**scottylad2** · 02-07-2011, 03:56 PM

Originally Posted by wglenn1

ok thats awesome. Last question I have.

I have this IF statement here:
=IF(D8="CB-01","1", "")

How do I convert this formula back to a number so I can sum up these columns?

Remove the quotes from 1, if the if evaluates to 1 then it'll need to show a 1, if you enclose that in "1" your actually turning the 1 into text and not a number