I have a genomic database with tests that need to be completed and I would like to write a function to see if the genetic primer matches the test in question.
Tests are usually in format of gene name (BRCA1) genomic position (c.404) then the nucleotide change (G>C)
We then have Primers in the format of: gene name + exon (BRCA1ex10) from a pull down menu that will bring in the genetic location which covers c.3640 to c. 4096
The tables will look like the following:
| TEST | Primer | Primer location |
|---|---|---|
| RAD51C c.404G>C | RAD51Cex02 | c.146 to c.405 |
| BRCA1 c.3869_3870delAA | BRCA1ex10-12 | c.3640 to c.4096 |
| FLCN c.1490_1491delTG | FLCNex12-13 | c.1301 to c.1538 |
| CHEK2 c.433C>T | CHEK2ex03ex04 | c.320 to c.592 |
I'm trying to pull out the first number only from the "TEST" column and wrote the following function
=MID(LEFT(H10,FIND({">","_","d","i"},H10)-2),FIND("c.",H10)+2,LEN(H10))
Since genetic changes will always have one of the following ">","_","d","i". This function returned the following values
| Genetic Position |
|---|
| 404 |
| #Value |
| #Value |
| 433 |
This function only seems to work for those genetic changes with the ">" character.
Once I have the genetic position I think I will be able to check if it is between the two numbers in the primer location quite easily. Thanks for the help
