Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can someone translate this formula pls
I used this formula to seperate numbers in a string and return the numbers. Example: original string "BD456". the outcome is "456" The formula worked just fine, but would someone please explain to me how it did that. The functions used don't follow the standard format that the stupid excel helper suggests; therefore, I didn't understand them. The function for a string in cell G2 is =MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"01234 56789")),1024) Thank you in advance. Mahmoud -- nebrass ------------------------------------------------------------------------ nebrass's Profile: http://www.excelforum.com/member.php...o&userid=32802 View this thread: http://www.excelforum.com/showthread...hreadid=532211 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can someone translate this formula pls
=MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"01234 56789")),1024)
SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")) will return an array of {7,4,5,6,11,12,13,14,15,16} - note that these numbers relate to the position in G2, but some are greater than 6 because we appended 0123456789 MIN will the return 4 - self explanatory MID(A1, SEARCH(...),ROW(...),1024) then returns 1024 characters starting at character 4, but as there are only 3 more, that is what you get, 123. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "nebrass" wrote in message ... I used this formula to seperate numbers in a string and return the numbers. Example: original string "BD456". the outcome is "456" The formula worked just fine, but would someone please explain to me how it did that. The functions used don't follow the standard format that the stupid excel helper suggests; therefore, I didn't understand them. The function for a string in cell G2 is =MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"01234 56789")),1024) Thank you in advance. Mahmoud -- nebrass ------------------------------------------------------------------------ nebrass's Profile: http://www.excelforum.com/member.php...o&userid=32802 View this thread: http://www.excelforum.com/showthread...hreadid=532211 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can someone translate this formula pls
Note, if G2 holds ABX123XYZ, you will get 123XYZ.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "nebrass" wrote in message ... I used this formula to seperate numbers in a string and return the numbers. Example: original string "BD456". the outcome is "456" The formula worked just fine, but would someone please explain to me how it did that. The functions used don't follow the standard format that the stupid excel helper suggests; therefore, I didn't understand them. The function for a string in cell G2 is =MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"01234 56789")),1024) Thank you in advance. Mahmoud -- nebrass ------------------------------------------------------------------------ nebrass's Profile: http://www.excelforum.com/member.php...o&userid=32802 View this thread: http://www.excelforum.com/showthread...hreadid=532211 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |