Pulling numbers from strings

guamlet

Board Regular
Joined
Dec 29, 2002
Messages
145
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Board Regular
Joined
Dec 29, 2002
Messages
145
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top