Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extracting the number from a text
Hi,
In a cell say C3, the value is such as S9(09)V(05). the value of that cell might be in one of the following format: (1) S(9)V(5) (2) S9(09)V(05) (3) S9(9)V(05) (4) S9(09)V(5) (5) S(11) My requirement is: i need to get the numbers extracted which, is present before & after the letter 'V' & enclosed in parantheses. In some cases, the letter V may not be there. (for ex: point 5 - here, only one number is there ie.11) This extraction should happen ONLY if the cell E3 has the value "COMP-3." Thanks in Advance. Venkatesh V |
#2
|
|||
|
|||
First number:
=IF($E$3="COMP-3",MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND ("(",A1)-1),"") Second number: =IF(AND($E$3="COMP-3",COUNTIF(A1,"*V*")),SUBSTITUTE(MID (A1,FIND("V",A1)+2,2),")",""),"") HTH Jason Atlanta, GA -----Original Message----- Hi, In a cell say C3, the value is such as S9(09)V(05). the value of that cell might be in one of the following format: (1) S(9)V(5) (2) S9(09)V(05) (3) S9(9)V(05) (4) S9(09)V(5) (5) S(11) My requirement is: i need to get the numbers extracted which, is present before & after the letter 'V' & enclosed in parantheses. In some cases, the letter V may not be there. (for ex: point 5 - here, only one number is there ie.11) This extraction should happen ONLY if the cell E3 has the value "COMP-3." Thanks in Advance. Venkatesh V . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Defining a number in a cell by text then subtracting it by the tex | Excel Worksheet Functions | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
What defines number or text | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |