Extracting numbers from a string in a cell without recurring to VBA code

Hi

I know how to extract numbers from a string using VBA. I recently learned about array functions and it ocurred to me that extracting numbers from a string might be accomplished with the use of such functions. So far I've been able to extract the numbers comparing the UCase and LCase of the string using an array function but I've been forced to place the result en an equal number of cells as to the number of characters of the string. I'd like to be able to concatenate each of the numbers and have the function's result in a single cell. My attempt at doing this is as follows:


=IF(EXACT(UCASE(MID(B5,{1\2\3\4\5\6\7\8\9\10},1)),LCASE(MID(B5,{1\2\3\4\5\6\7\8\9\10},1)))*CODE(MID(B5,{1\2\3\4\5\6\7\8\9\10},1))<>0,1,2)


the {1\2\3\4\5\6\7\8\9\10} part means I'll evaluate strings made of up to 10 characters (Though I'd like it very much if this could be done in a not fixed fashion, but accordingly to the string's length)

So far, when evaluating the formula, for the string: "string 125" I come up with a vector : IF ({0|0|0|0|0|0|32|49|50|53}<>0,1,2) the number zeros meaning no coincidence between the UCASE and LCASE (in other words letters), the number 32 a blank space, numbers 49,50 and 53 representing the ascii code for the numbers 1,2 and 5.

THE PROBLEM IS THAT FROM THERE, I DON'T KNOW HOW TO CONCATENATE THE CHARACTERS OF THOSE NUMBERS INTO A RESULT STRING THAT CONTAINS THE BLANK SPACE AND THE NUMBERS SO THAT THE RESULT CAN BE CONVERTED INTO A NUMERIC VALUE USING THE VAL FUNCTION.



Thanks for any insight.

Hi MarvinP!

Thank you very much for your advice! it works!

Hi FDibbins!

Well I'll be damned!. Don't really understand what you did but it works exactly like I wanted it to work!


Thank You Very Much!