## [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

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?

Last edited by silverb on Mon Mar 14, 2011 10:48 am, edited 3 times in total.
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### 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.
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### 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   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 iend function`
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Extract a number from a text string without knowing posi

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.

I need a formula.
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### Re: Extract a number from a text string without knowing posi

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 iend function`

Thanks,

shall i create a new macro and insert this code? i'm starting with oocalc
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### 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; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Extract a number from a text string without knowing posi

Zizi64 wrote:Just copy the code into a new module of your document or into a module of MyMacros-Standard library,

Thanks, and to call it from the cell?
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### 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)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### 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
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### Re: Extract a number from a text string without knowing posi

Did you forget the leading "=" (equals) sign?
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 28981
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### 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
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### 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?
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### Re: Extract a number from a text string

What you mean:
"is it possible to change the column after every different number is found?"

Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Extract a number from a text string

Zizi64 wrote:What you mean:
"is it possible to change the column after every different number is found?"

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??

Because if i use int function i got #value where there is only text.
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### Re: Extract a number from a text string

Because if i use int function i got #value where there is only text.

You need modify the basic code:

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 if Only_Numbers = "" then Only_Numbers = "0"end function`
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Extract a number from a text string

Thank you so much Zizi
Openoffice 3.2 - Kubuntu 10.04
silverb

Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

### 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:
=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 2.4.X on Ubuntu 8.x + WindowsXP
Yumi

Posts: 14
Joined: Fri Aug 29, 2008 3:54 am

### 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...
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Extract a number from a text string without knowing posi

Zizi64 wrote:Just copy the code into a new module of your document or into a module of MyMacros-Standard library,

help me with this - please -
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
openOffice 4.0.0 on macOS
me231

Posts: 80
Joined: Sat Nov 02, 2013 4:31 pm

### Re: [Solved] Extract a number from a text string

Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****Sub MainFunction 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 iend functionEnd Sub`

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)
Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****Sub MainEnd SubFunction 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 iend function`

You can call the function by the formula:
Code: Select all   Expand viewCollapse view
`=Only_Numbers(CellReference)`
Last edited by Zizi64 on Fri Feb 08, 2019 12:19 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### 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:
Code: Select all   Expand viewCollapse view
`=RIGHT(A3;LEN(A3)-SEARCH("\$";A3))`

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 : ".") .
Last edited by Zizi64 on Fri Feb 08, 2019 1:25 am, edited 2 times in total.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### 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
openOffice 4.0.0 on macOS
me231

Posts: 80
Joined: Sat Nov 02, 2013 4:31 pm

### Re: [Solved] Extract a number from a text string

Code: Select all   Expand viewCollapse view
`=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, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26751
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### 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
openOffice 4.0.0 on macOS
me231

Posts: 80
Joined: Sat Nov 02, 2013 4:31 pm

### 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
Code: Select all   Expand viewCollapse view
`=VALUE(RIGHT(A3;LEN(A3)-FIND("\$";A3)))`

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26751
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: [Solved] Extract a number from a text string

awesome - thank you
openOffice 4.0.0 on macOS
me231

Posts: 80
Joined: Sat Nov 02, 2013 4:31 pm

### Re: [Solved] Extract a number from a text string

Thank you Zizi64 and Villeroy, very helpful!!
OpenOffice 4.x on Win10
ChrisD

Posts: 1
Joined: Sat May 18, 2019 11:24 pm