Excel 2010 - convert <1 to 0 and >xxx to numeric value 3

[cjbrown815]

Hello,
I have a column that returns results that are numeric, text, string...you name it. I need to create a line graph. If the result is a <1 or <150 or whatever, its a 0. If its a "greater than" (example: >59000, >150, > whatever) I need it to be the numeric value that follows the ">" sign. If the result is TNTC then I need it to be 100,000. Any idea how I can get this done? thanks

DATA EXAMPLE
NR
NF
POS
P
1234
778900000
<1
<100
>150
TNTC

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[VulcanJedi]

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

Public Function ConvertForGraph(theValue)

If InStr(1, theValue, "<1", vbBinaryCompare) > 0 Then
    ConvertForGraph = 0
ElseIf InStr(1, theValue, "TNTC", vbBinaryCompare) > 0 Then
    ConvertForGraph = 100000
ElseIf InStr(1, theValue, ">", vbBinaryCompare) > 0 Then
    ConvertForGraph = Right(theValue, (Len(theValue) - 1))
Else
    ConvertForGraph = theValue
End If

End Function

 

[SkipVought]

Hi,

Trouble is that you have absolutely no numeric values in your column! It's all text!

Since you did not post in forum707, I assume you want a spreadsheet solution.

It often causes problems to have text and numbers in the same column. Excel processes data most often in columns.

So I'd make another column with the following formula for the numeric part:

=if(left(a1,1)="<",0,if(left(a1,1)="<",right(a1,len(a1)-1),""))

 

[cjbrown815]

How do I apply this to the entire column?

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Copy 'n' paste.

The formula assumes data in row 1 column A and formula in row 1

 

[cjbrown815]

Hi Skip,
thanks for your time.
The data is coming in from a SQL DB in the original format, the SQL DB field is a string. Your formula returned all zeros


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[Deniall]

I think you'll get closer if you change Skip's mistyped second "<" to a ">".
You'll probably then have to expand the final "" to cope with the various possible text entries contaminating your data.

 

[SkipVought]

Sorry for the misstype.

I constructed the formula to return only NUMBERS.

=if(left(a1,1)="<",0,if(left(a1,1)=">",value(right(a1,len(a1)-1)),""))

 

[cjbrown815]

Skip,
Very close to the right solution. The conversion is working, but its not bringing the numeric values over as well, I have numbers like 4900, 29, 556 that need to be included in the graph, is it possible to bring those over to the new column so I can just pick that column for my graph data? Or do you have a better idea?

thank you again for your time,

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Include another if(), testing a1 to be numeric ie ISNUMBER(a1)

 

[cjbrown815]

sorry Skip, I'll need to be spoon fed. I tried to get this to work and it is not, I cant figure out how to nest this in the current syntax. Not sure where to put it. thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[zelgar]

=if(isnumber(a1),a1,if(left(a1,1)="<",0,if(left(a1,1)=">",value(right(a1,len(a1)-1)),"")))