How to extract the number from the long strings in cells of a sheet 1

[korper]

I got a sheet full of cells, my job is to get the number from each cell, which contains a long string e.g.("what i really need is only the number of 1234 mm&quot,
I made a small function to find and cut the spaces bit by bit, but it stop at "of 1234", and find no space any more!!! A funny thing is that if write such string in a cell and do the applet, it just can make out "1234"!!
Is there a symbol of "+/-" has occupied the space or a devil?
Any advice is appreciated!

 

[prex]

Try this one (f is your string):

Function look_for_number(f)
Dim s
  look_for_number = 0
  For Each s In Split(f)
    If Val(s) > 0 Then
      look_for_number = Val(s)
      Exit Function
    End If
  Next
End Function

prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[prex]

I inspected the file you sent me.
The mistery for your inability to extract the number from the string resides in the fact that apparently all numbers in your sheet are surrounded by characters 160.
This character looks as a space (character 32), but it is not understood as such by the software.
I don't know why this happens, the reason should be in the routine you used to generated those strings.
If all your numbers are like that, the solution is simple: in the function [tt]split[/tt] you can specify the delimiter, and you can give a string composed of a chr(160) that you can either code as [tt]chr(160)[/tt] or generate with Alt+numeric pad. Otherwise you'll be obliged to make a double extraction, once with chr(160), then with chr(32) (the default for [tt]split[/tt]).
prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[korper]

Actually the character can not be defined as Chr(?), So i have tried to get it from the string text itself. and then find and compare with it throughout the String.
So i got it!
Thanks!