C# .NET - excel formula or excel VBA code to Retrieve number from a text mixed with number
Asked By Cherifa Hima
21-Jan-10 04:08 PM
Hi,
Is there an excel formula or excel VBA code to retrieve numbers from a text mixed with numbers knowing that the position of the numbers can change from one word to another, for example :
Sp WSW (58.3) m/s
Sp NE (48) m/s
Sp E (60) m/s
Thanks.
excel formula or excel VBA code to Retrieve number from a text mixed with number
=REPLACE(LEFT(A8,LOOKUP(10,MID(A8,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(A8&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0
Paste that in the formula bar to accept. Then hit CTRL+SHIFT+ENTER to make it as an array.
Had to do a little research, so the exact formula is from:
http://www.excelforum.com/excel-worksheet-functions/630231-extract-number-from-alphanumeric-string.html
The CTRL+SHIFT+ENTER tip came from:
http://office.microsoft.com/en-us/excel/HA011549011033.aspx
The cell number that I am pointing to is A8...
Would like to take credit, but this is more about helping others getting their questions answered...
Jonathan VH replied to Cherifa Hima
If the numbers will always be within parentheses, as in your examples:
=VALUE(MID(LEFT(A1,FIND(")",A1)-1),FIND("(",A1)+1,99))
excel formula or excel VBA code to Retrieve number from a text mixed with number
Thanks to both of you. Your formulas work nice. God Bless.
run into this one! But, a problem came up at the office with designing a worksheet where difference between two time values is calculated. The problem was that a user may Ctrl+Shift +Enter): = IF(ISNUMBER(E14), E14, TIMEVALUE(LEFT(E14, MATCH(1, (MID(E14, ROW(INDIRECT("1:"&LEN(E14))), 1)<> ":")*ISERROR(MID(E14, ROW(INDIRECT("1:"&LEN(E14))), 1)*1), 0)-1)&" (MID(D14, ROW(INDIRECT("1:"&LEN(D14))), 1)<> ":")*ISERROR(MID(D14, ROW(INDIRECT("1:"&LEN(D14))), 1)*1), 0)-1)&" Where D14 is Time In, and E14 is So, one more thing to look out for when working with the new version. Excel Worksheet Discussions Excel 2007 (1) SUBSTITUTE (1) Worksheet (1) TIMEVALUE (1) INDIRECT (1) ISNUMBER (1) ISERROR (1) LOOKUP (1) You could use Data
the content of one cell into two cells example : 6'X3' = 6 and 3 Excel Worksheet Discussions Excel (1) SUBSTITUTE (1) INDIRECT (1) LOOKUP (1) LEFT (1) ROW (1) LEN (1) MID (1) Left part: = LOOKUP(6.022*10^23, - -LEFT(A1, ROW(INDIRECT("1:"&LEN(A1))))) Right part: = LOOKUP(6.022*10^23, - -RIGHT SUBSTITUTE(A1, "'", ""), ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1, "'", "")))))) - - Regards! Stefi ???cosmic??? ezt ??rta: Try = - -SUBSTITUTE(LEFT(A1, SEARCH("X", A1)-1), "'", ) = - -SUBSTITUTE(MID(A1, SEARCH("X", A1)+1, 10), "'", ) - - Jacob
12, IF(slope = "60-100%", Area*$Q$13, IF(slope = "> 100%", Area*$Q$14)))))) Excel Worksheet Discussions Excel 2007 (1) Excel (1) SUBSTITUTE (1) INDIRECT (1) ISERROR (1) LEFT (1) FIND (1) TRIM (1) I assume that your data is In TABLE 1 (Sheet1) Paste this formula in H2 cell = IF(ISERROR(VALUE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A2, "???", "-"), "+", "")), FIND("-", TRIM(SUBSTITUTE(SUBSTITUTE(A2, "???", "-"), "+", "")))-1))), VALUE(SUBSTITUTE(SUBSTITUTE(A2, "???", "-"), "+", "")), VALUE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A2, "???", "-"), "+", "")), FIND("-", TRIM(SUBSTITUTE(SUBSTITUTE(A2, "???", "-"), "+", "")))-1))) Copy
Reference a Worksheet Tab in Formula Excel How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan Excel Worksheet Discussions Application.Caller.Parent.Index (1) Microsoft Excel (1) Sheets (1) Excel (1) Chart (1) SUBSTITUTE (1 Worksheet (1) INDIRECT (1) To get, in a formula, the current sheet name, you can use the following
to the cell that contains this formula. Here is my problem. I am needing to substitute a lot of values, and the formula is waaay to long. Here is what it work, excel will not accept something this long: IFERROR(if(F19, IF(backwards_compatible, "<description> <![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE