Pulling numbers from strings

[guamlet]

I've used something similar to the following formula (reality is these values come from different worksheets) to get some data to appear as the "average +/- standard deviation, n="

=C3 & " +/-" & D4 & ", n=" & E4

Now I would like to go through the result, look at the first three characters, and if they are greater than a number, change the color of the cell. I tried using Left(Cell(i,j),3), but that only returns strings. So then I tried CDbl to convert the string to a number, which worked great until I encountered areas of the spreadsheet that had actual text like "Muscle". These became numbers like 1.1 or 1.8 when converted to the Double format.

Any ideas on how to make this work?

 

[indiantrix]

I'm headed for bed, but before I go... Howabout using your LEFT formula like this... =1*LEFT(A4,3) The multiplier of "1" may coerce any text strings to become numbers if numbers are in the string. Good Luck!
and Good night! Larry

 

[tusharm]

So, why not just use the value in C3?

I've used something similar to the following formula (reality is these values come from different worksheets) to get some data to appear as the "average +/- standard deviation, n="

=C3 & " +/-" & D4 & ", n=" & E4

Now I would like to go through the result, look at the first three characters, and if they are greater than a number, change the color of the cell. I tried using Left(Cell(i,j),3), but that only returns strings. So then I tried CDbl to convert the string to a number, which worked great until I encountered areas of the spreadsheet that had actual text like "Muscle". These became numbers like 1.1 or 1.8 when converted to the Double format.

Any ideas on how to make this work?

 

[guamlet]

I knew that was going to be the first question back from the board....

What I posted was the reader's digest version...here's the whole story...

This data is RNA expression for approximately 32 different genes that belong to 5 different categories (e.g. muscle, fat, bone). I have an access database that is holding the information in a query, which puts the gene names in alphabetical order. I copy the query result to excel as-is. I have one sheet for the average, one for the standard deviation, and one for the number of PCR runs that the data came from.

Once I use that formula to build the average +/- standard deviation, n= X, the tables are sorted by category and grouped for export to word as little tables for my thesis (in fact these are going to be dynamic links to Word so that as more data comes in, the tables get their values adjusted, and I don't have to copy/paste a billion times). I thought about holding the values of C3, etc in an array but since the cells that I want to color are now out of order with respect to the original I figured that wouldn't work. The easiest thing I thought to do was to just go through the whole worksheet (column and row headers and data all together), look at the first three characters, if they're numerical, ask if they're greater than 1.3 or less than 0.7, and color the cell appropriately. The problem comes that once I built those cells from that formula (average +/- standard deviation, n=X), even though the cells sending the numbers are still "numbers" to Excel, the built-up cell value is now a string...so I tried to use "CDbl", but all the headers are also strings, and will create a number when "CDbl" is used, which mucks the whole thing up.

So, in essence I don't think I can use C3 to run the whole thing...any suggestions?