[Solved] Extract a number from a text string

Discuss the spreadsheet application

[Solved] Extract a number from a text string

Postby silverb » Fri Mar 11, 2011 2:51 pm

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

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

Postby acknak » Fri Mar 11, 2011 4:22 pm

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

Postby Zizi64 » Fri Mar 11, 2011 5:27 pm

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
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby silverb » Fri Mar 11, 2011 5:32 pm

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

Postby silverb » Fri Mar 11, 2011 5:36 pm

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


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

Postby Zizi64 » Fri Mar 11, 2011 5:40 pm

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.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby silverb » Fri Mar 11, 2011 5:41 pm

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

Postby Zizi64 » Fri Mar 11, 2011 5:45 pm

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.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby silverb » Fri Mar 11, 2011 5:52 pm

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

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

Postby RoryOF » Fri Mar 11, 2011 5:54 pm

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
User avatar
RoryOF
Moderator
 
Posts: 28412
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby silverb » Fri Mar 11, 2011 5:55 pm

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]

Postby silverb » Fri Mar 11, 2011 6:00 pm

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

Postby Zizi64 » Fri Mar 11, 2011 6:10 pm

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

please give us more details...
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Extract a number from a text string

Postby silverb » Fri Mar 11, 2011 6:36 pm

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

Re: Extract a number from a text string

Postby Zizi64 » Fri Mar 11, 2011 7:51 pm

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.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Extract a number from a text string

Postby silverb » Mon Mar 14, 2011 10:40 am

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

Postby Yumi » Sat Mar 16, 2013 4:48 pm

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: 10
Joined: Fri Aug 29, 2008 3:54 am

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

Postby Zizi64 » Wed Mar 20, 2013 9:56 am

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.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby me231 » Thu Feb 07, 2019 7:54 pm

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 7 times
openOffice 4.0.0 on macOS
me231
 
Posts: 78
Joined: Sat Nov 02, 2013 4:31 pm

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

Postby Zizi64 » Thu Feb 07, 2019 11:50 pm

Your macro code:

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   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.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Zizi64 » Fri Feb 08, 2019 12:08 am

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.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and 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.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby me231 » Fri Feb 08, 2019 1:09 am

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

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

Postby Villeroy » Fri Feb 08, 2019 3:02 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby me231 » Sun Feb 10, 2019 11:33 pm

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

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

Postby Villeroy » Mon Feb 11, 2019 12:56 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby me231 » Mon Feb 11, 2019 4:49 am

awesome - thank you
openOffice 4.0.0 on macOS
me231
 
Posts: 78
Joined: Sat Nov 02, 2013 4:31 pm


Return to Calc

Who is online

Users browsing this forum: lcwilcox and 11 guests