Function Only_Numbers(Text_From_Cell as string) as string
Numbers_And_Signs = "0123456789 .+-*/"
n = len(Text_From_Cell)
Only_Numbers = ""
For i = 1 to n
ActChar = Mid(Text_From_Cell,i,1)
if Instr(Numbers_And_Signs,ActChar)<>0 then Only_Numbers = Only_Numbers + ActChar
next i
end function
acknak wrote:Do you need a formula to extract the numbers, or can you just do it once?
If you only need it once, you can do it with Find & Replace.
Zizi64 wrote:Use something similar Basic function as an user defined Cell function:
- Code: Select all Expand viewCollapse view
Function Only_Numbers(Text_From_Cell as string) as string
Numbers_And_Signs = "0123456789 .+-*/"
n = len(Text_From_Cell)
Only_Numbers = ""
For i = 1 to n
ActChar = Mid(Text_From_Cell,i,1)
if Instr(Numbers_And_Signs,ActChar)<>0 then Only_Numbers = Only_Numbers + ActChar
next i
end function
Zizi64 wrote:Just copy the code into a new module of your document or into a module of MyMacros-Standard library,
Zizi64 wrote:for example:
Tools - Macro - Organize Macros - OpenOffice.org Basic - MyMacros - Standard ...
YES,
when your string located in A1 then
B1 =Only_Numbers(A1)
Zizi64 wrote:What you mean:
"is it possible to change the column after every different number is found?"
please give us more details...
Because if i use int function i got #value where there is only text.
Function Only_Numbers(Text_From_Cell as string) as string
Numbers_And_Signs = "0123456789 .+-*/"
n = len(Text_From_Cell)
Only_Numbers = ""
For i = 1 to n
ActChar = Mid(Text_From_Cell,i,1)
if Instr(Numbers_And_Signs,ActChar)<>0 then Only_Numbers = Only_Numbers + ActChar
next i
if Only_Numbers = "" then Only_Numbers = "0"
end function
=ONLY_NUMBERS(A3874)
Function Only_Numbers(Text_From_Cell as string) as string
Numbers_And_Signs = "01234567890"
n = len(Text_From_Cell)
Only_Numbers = ""
For i = 1 to n
ActChar = Mid(Text_From_Cell,i,1)
if Instr(Numbers_And_Signs,ActChar)<>0 then Only_Numbers = Only_Numbers + ActChar
next i
if Only_Numbers = "" then Only_Numbers = "0"
end function
Zizi64 wrote:Just copy the code into a new module of your document or into a module of MyMacros-Standard library,
REM ***** BASIC *****
Sub Main
Function Only_Numbers(Text_From_Cell as string) as string
Numbers_And_Signs = "0123456789 .+-*/"
n = len(Text_From_Cell)
Only_Numbers = ""
For i = 1 to n
ActChar = Mid(Text_From_Cell,i,1)
if Instr(Numbers_And_Signs,ActChar)<>0 then Only_Numbers = Only_Numbers + ActChar
next i
end function
End Sub
REM ***** BASIC *****
Sub Main
End Sub
Function Only_Numbers(Text_From_Cell as string) as string
Numbers_And_Signs = "0123456789 .+-*/"
n = len(Text_From_Cell)
Only_Numbers = ""
For i = 1 to n
ActChar = Mid(Text_From_Cell,i,1)
if Instr(Numbers_And_Signs,ActChar)<>0 then Only_Numbers = Only_Numbers + ActChar
next i
end function
=Only_Numbers(CellReference)
=RIGHT(A3;LEN(A3)-SEARCH("$";A3))
=VALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"");".";MID(1/2;2;1)))
=VALUE(RIGHT(A3;LEN(A3)-FIND("$";A3)))
Users browsing this forum: carmsbody, croko, Lupp and 36 guests