[Solved] Extract a number from a text string

Discuss the spreadsheet application
Post Reply
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

[Solved] Extract a number from a text string

Post by silverb »

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 :cry:

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
User avatar
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

Post by acknak »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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.
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

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

Post by silverb »

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

Post by silverb »

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
Thanks,

shall i create a new macro and insert this code? i'm starting with oocalc
Openoffice 3.2 - Kubuntu 10.04
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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.
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

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

Post by silverb »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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; 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.
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

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

Post by silverb »

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 :cry: i enter in a cell Only_Numbers(A1) but nothing happens, the cell shows this text.
Openoffice 3.2 - Kubuntu 10.04
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Post by RoryOF »

Did you forget the leading "=" (equals) sign?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

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

Post by silverb »

Solved!!! i'm nerd i was forgetting the = :oops:
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]

Post by silverb »

One last question,

is it possible to change the column after every different number is found?
Openoffice 3.2 - Kubuntu 10.04
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Extract a number from a text string

Post by Zizi64 »

What you mean:
"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.
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

Re: Extract a number from a text string

Post by silverb »

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

please give us more details...
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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Extract a number from a text string

Post by Zizi64 »

Because if i use int function i got #value where there is only text.
You need modify the basic code:

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.
silverb
Posts: 11
Joined: Fri Mar 11, 2011 2:43 pm

Re: Extract a number from a text string

Post by silverb »

Thank you so much Zizi
Openoffice 3.2 - Kubuntu 10.04
Yumi
Posts: 26
Joined: Fri Aug 29, 2008 3:54 am

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

Post by Yumi »

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 4.1.6 on Linux Mint 19.1 Tessa
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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; 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.
me231
Posts: 118
Joined: Sat Nov 02, 2013 4:31 pm

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

Post by me231 »

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
(12.33 KiB) Downloaded 182 times
openOffice 4.0.0 on macOS
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

Your macro code:

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

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
You can call the function by the formula:

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.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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

=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; 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.
me231
Posts: 118
Joined: Sat Nov 02, 2013 4:31 pm

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

Post by me231 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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
me231
Posts: 118
Joined: Sat Nov 02, 2013 4:31 pm

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

Post by me231 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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

=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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
me231
Posts: 118
Joined: Sat Nov 02, 2013 4:31 pm

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

Post by me231 »

awesome - thank you
openOffice 4.0.0 on macOS
ChrisD
Posts: 1
Joined: Sat May 18, 2019 11:24 pm

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

Post by ChrisD »

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