[Solved] Extract a number from a text string
[Solved] Extract a number from a text string
Hello,
this is driving me mad. I need to search and extract a number/numbers from a text cell. I've searched a lot of info but everything fails
Example:
text:
150 x model 1Gb etc.
150/200xpart 2g etc.
samples
aaa 123 asdada
result:
150 or if can be 150 / 1 (this would be awesome)
150 or 150 / 200 / 2 (also awesome)
"blank"
123
btw if only first number is extracted it's ok.
Somebody can help me with this oocalc formula?
Thanks in advance!
this is driving me mad. I need to search and extract a number/numbers from a text cell. I've searched a lot of info but everything fails
Example:
text:
150 x model 1Gb etc.
150/200xpart 2g etc.
samples
aaa 123 asdada
result:
150 or if can be 150 / 1 (this would be awesome)
150 or 150 / 200 / 2 (also awesome)
"blank"
123
btw if only first number is extracted it's ok.
Somebody can help me with this oocalc formula?
Thanks in advance!
Last edited by silverb on Mon Mar 14, 2011 10:48 am, edited 3 times in total.
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string without knowing posi
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.
If you only need it once, you can do it with Find & Replace.
AOO4/LO5 • Linux • Fedora 23
Re: Extract a number from a text string without knowing posi
Use something similar Basic function as an user defined Cell function:
Code: Select all
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
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract a number from a text string without knowing posi
I need a formula.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.
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string without knowing posi
Thanks,Zizi64 wrote:Use something similar Basic function as an user defined Cell function:
Code: Select all
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
shall i create a new macro and insert this code? i'm starting with oocalc
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string without knowing posi
Just copy the code into a new module of your document or into a module of MyMacros-Standard library,
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract a number from a text string without knowing posi
Thanks, and to call it from the cell?Zizi64 wrote:Just copy the code into a new module of your document or into a module of MyMacros-Standard library,
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string without knowing posi
for example:
Tools - Macro - Organize Macros - OpenOffice.org Basic - MyMacros - Standard ...
YES,
when your string located in A1 then
B1 =Only_Numbers(A1)
Tools - Macro - Organize Macros - OpenOffice.org Basic - MyMacros - Standard ...
YES,
when your string located in A1 then
B1 =Only_Numbers(A1)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract a number from a text string without knowing posi
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)
mmm there's something wrong i enter in a cell Only_Numbers(A1) but nothing happens, the cell shows this text.
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string without knowing posi
Did you forget the leading "=" (equals) sign?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Extract a number from a text string without knowing posi
Solved!!! i'm nerd i was forgetting the =
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string [SOLVED]
One last question,
is it possible to change the column after every different number is found?
is it possible to change the column after every different number is found?
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string
What you mean:
"is it possible to change the column after every different number is found?"
please give us more details...
"is it possible to change the column after every different number is found?"
please give us more details...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract a number from a text string
Forget about is not necessary. Is it possible to avoid having a 0 as result with the cell is blank and get the result as an integer??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.
Openoffice 3.2 - Kubuntu 10.04
Re: Extract a number from a text string
You need modify the basic code:Because if i use int function i got #value where there is only text.
Code: Select all
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
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Extract a number from a text string
I use the formula on this string "Tiergartenweg797953Konigsheim". Works but it ignors the number 9 in all strings. The result given is "7753".
Where is the problem?
Formula:
Makro:
Where is the problem?
Formula:
=ONLY_NUMBERS(A3874)
Makro:
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
OOo 4.1.6 on Linux Mint 19.1 Tessa
Re: [Solved] Extract a number from a text string
Your code works fine for me, in LO3.6.4, in LO3.4.0Portable, in AOO3.4.1.
The result is:
797953
Are you using OOo 2.4.X ??
That version is very-very-very old...
The result is:
797953
Are you using OOo 2.4.X ??
That version is very-very-very old...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract a number from a text string without knowing posi
help me with this - please -Zizi64 wrote:Just copy the code into a new module of your document or into a module of MyMacros-Standard library,
the attached is my SS - I am guessing it has the macro atteched -
it does not work for me....
I am in cell C3 adjacent to string - run macro and nothing happens...becuase i do not know how to alter code so that it is running in cell A3....
the returned value in B3 is the excel version - =right (;)
but it is not sophisticated enough to handle different length values
" user defined Cell function"
- Attachments
-
- extract number module clac.ods
- (12.33 KiB) Downloaded 182 times
openOffice 4.0.0 on macOS
Re: [Solved] Extract a number from a text string
Your macro code:
Do not put the code lines of the FUNCTION into a SUBroutine.
A Function is a function, and a Sub is a subroutine. Never mix them.
(Actually the predefined sub named "Main" not needed for you)
You can call the function by the formula:
Code: Select all
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
A Function is a function, and a Sub is a subroutine. Never mix them.
(Actually the predefined sub named "Main" not needed for you)
Code: Select all
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
Code: Select all
=Only_Numbers(CellReference)
Last edited by Zizi64 on Fri Feb 08, 2019 12:19 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Extract a number from a text string
My Function will search and collect all of numeric characters and some other signs in a long string. But it is inappropriate for your task: Your strings contains some other numeric characters in the unwanted part of the string. I suppose it: You need search the $ character, and you must to determine the position of the $ character, the length of the string - just substitute the position value from the length value -, and the RIGHT() function will work with the dinamic (calculated) parameter. I hope, there is only one $ character in your long strings.
In cell C3:
Note: The result IS a string, but not a numeric value! If you need a numeric value, then you need convert the string result to a numeric value by usage other built-in functions, or an another macro function.
Note 2: The function =VALUE() can convert the strings to a numeric value, but the decimal separator must match to the local settings (De, Hu, ...etc : "," En, ...etc : ".") .
In cell C3:
Code: Select all
=RIGHT(A3;LEN(A3)-SEARCH("$";A3))
Note 2: The function =VALUE() can convert the strings to a numeric value, but the decimal separator must match to the local settings (De, Hu, ...etc : "," En, ...etc : ".") .
Last edited by Zizi64 on Fri Feb 08, 2019 1:25 am, edited 2 times in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Extract a number from a text string
the =right is quick and I like what you showed - the issue is as you mention not elegant - non integer
I am trying to get the macro to do the following -
look at a column with mixed text and numbers and find $###,###.00 or some variant then list these in the adjacent column of my choice - but since the values of the dollar amounts varies - the right function is limited...but thank you
I am trying to get the macro to do the following -
look at a column with mixed text and numbers and find $###,###.00 or some variant then list these in the adjacent column of my choice - but since the values of the dollar amounts varies - the right function is limited...but thank you
openOffice 4.0.0 on macOS
Re: [Solved] Extract a number from a text string
Code: Select all
=VALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"");".";MID(1/2;2;1)))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Extract a number from a text string
this works great - is a formula not marco and will work great - so a little education if you have time =
Value - converts the result from text to numeric
Substitute 1 - looks at result of "right function - places "." or mid value
right fucntion extrating length of a string in A3 after the "$"
ami close
Value - converts the result from text to numeric
Substitute 1 - looks at result of "right function - places "." or mid value
right fucntion extrating length of a string in A3 after the "$"
ami close
openOffice 4.0.0 on macOS
Re: [Solved] Extract a number from a text string
LEN(A3)-FIND("$";A3) returns the position of the $ from the right end.
RIGHT(A3;LEN(A3)-FIND("$";A3)) returns the numeric English string after the $, say 1,234.98
SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"") removes the comma: 1234.98
MID(1/2;2;1) returns the second character of 1/2 which is either decimal comma (0,5) or decimal point (0.5). I don't know which one is valid with your setup.
SUBSTITUTE(SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"");".";MID(1/2;2;1)) substitutes the point with whatever is the true decimal separator. It may convert 1234.98 into 1234,98 if the comma is your decimal separator. If your decimal separator is the point, this substistution does nothing (replace point with point).
VALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"");".";MID(1/2;2;1))) returns the numeric value of that valid numeric string.
If your locale happens to be English, the formula can be reduced to
RIGHT(A3;LEN(A3)-FIND("$";A3)) returns the numeric English string after the $, say 1,234.98 and VALUE can convert this string into a number if (and only if) the locale setting is some flavour of English. On my system I have to remove the comma and substitute the decimal point with a decimal comma.
RIGHT(A3;LEN(A3)-FIND("$";A3)) returns the numeric English string after the $, say 1,234.98
SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"") removes the comma: 1234.98
MID(1/2;2;1) returns the second character of 1/2 which is either decimal comma (0,5) or decimal point (0.5). I don't know which one is valid with your setup.
SUBSTITUTE(SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"");".";MID(1/2;2;1)) substitutes the point with whatever is the true decimal separator. It may convert 1234.98 into 1234,98 if the comma is your decimal separator. If your decimal separator is the point, this substistution does nothing (replace point with point).
VALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(A3;LEN(A3)-FIND("$";A3));",";"");".";MID(1/2;2;1))) returns the numeric value of that valid numeric string.
If your locale happens to be English, the formula can be reduced to
Code: Select all
=VALUE(RIGHT(A3;LEN(A3)-FIND("$";A3)))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Extract a number from a text string
Thank you Zizi64 and Villeroy, very helpful!!
OpenOffice 4.x on Win10