How to extract the number from the long strings in cells of a sheet
How to extract the number from the long strings in cells of a sheet
(OP)
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"),
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!
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!





RE: How to extract the number from the long strings in cells of a sheet
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
RE: How to extract the number from the long strings in cells of a sheet
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 split you can specify the delimiter, and you can give a string composed of a chr(160) that you can either code as chr(160) 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 split).
prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design
RE: How to extract the number from the long strings in cells of a sheet
So i got it!
Thanks!