[Solved] Extract numbers from a string of alphanumeric text

Discuss the spreadsheet application

[Solved] Extract numbers from a string of alphanumeric text

Postby slsleng » Thu Apr 26, 2012 5:27 am

Can anyone help me with a formula to extract the number from a string of text that contains alphanumeric?

Example : The string text - KV3006ZBRH
I need a formula to extract the numeric numbers; ie 3006 from the string. The positioning of the first numeric number is not fixed and the length of the string or the numeric numbers are not fixed either.
Last edited by Hagar Delest on Thu Apr 26, 2012 11:07 am, edited 3 times in total.
Reason: tagged [Solved].
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby JohnSUN-Pensioner » Thu Apr 26, 2012 6:48 am

Hi and welcome!
I think that a formula
=VALUE(MID(A1;SEARCH("[0-9]";A1;1);SEARCH("[^0-9]";A1;SEARCH("[0-9]";A1;1))-SEARCH("[0-9]";A1;1)))
must return good result for string in cell A1
Don't forget to check "regular expressions" in the Calculations section in the Preferences.
I may not have a lot to give but what I got I'll give to you...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Extracting the numbers from a string of alphanumeric tex

Postby slsleng » Thu Apr 26, 2012 7:06 am

Hi,

Thanks for the formula but it gives me error when I test it out.

I have attached a sample of the file. The data is in column G and the formula is entered in column I.
Attachments
sample.ods
(20.45 KiB) Downloaded 136 times
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby JohnSUN-Pensioner » Thu Apr 26, 2012 7:20 am

Oh! In column G no letters after digits... So SEARCH("[^0-9]"... return error.
I'm apply formula to column C. It' s wrong?

Also you can add "stopper" to this SEARCH():
=VALUE(MID(G10;SEARCH("[0-9]";G10;1);SEARCH("[^0-9]";G10&"A";SEARCH("[0-9]";G10;1))-SEARCH("[0-9]";G10;1)))
If "no letters after digits" than SEARCH will find our stopper "A" :lol:
Attachments
sample.ods
(20.8 KiB) Downloaded 127 times
I may not have a lot to give but what I got I'll give to you...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Extracting the numbers from a string of alphanumeric tex

Postby karolus » Thu Apr 26, 2012 8:07 am

Hi
Why not direct on Column C:
Code: Select all   Expand viewCollapse view
=MID(C10;SEARCH("[0-9]+";C10);SEARCH("[0-9][A-Z]";C10)-SEARCH("[0-9]+";C10)+1)*1


Karo
AOO4, Libreoffice 4.2 on Linux Mint16
User avatar
karolus
Volunteer
 
Posts: 350
Joined: Sat Jul 02, 2011 9:47 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby slsleng » Thu Apr 26, 2012 8:41 am

Thanks, John for the formula. But how come when I copy the formula and paste it to the actual worksheet it gives error?

I'm still new to open office and have not get use to it yet.
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby karolus » Thu Apr 26, 2012 8:53 am

Hi
Change the Celladdresses in the Formula to that with contents the String...
In your Sample.ods the Strings starts in C10.

Karo
AOO4, Libreoffice 4.2 on Linux Mint16
User avatar
karolus
Volunteer
 
Posts: 350
Joined: Sat Jul 02, 2011 9:47 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby slsleng » Thu Apr 26, 2012 8:55 am

Yes, I have done that. Yet it still gives error.

If the formula is copy-&-paste in the same worksheet, it works fine.

I really don't understand open office. Since I can't copy-&-paste the formula to another worksheet in another workbook, I copy the whole worksheet into my original workbook. Once the worksheet is copied to my original worksheet, the all the formulas give error. :evil:
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby karolus » Thu Apr 26, 2012 9:53 am

Hi
Check in your Originaldocument →Tools→Options→OOocalc→Calculate [x]allow Regular Expressions in Formula

These Options are stored individually by Document !

Karo
AOO4, Libreoffice 4.2 on Linux Mint16
User avatar
karolus
Volunteer
 
Posts: 350
Joined: Sat Jul 02, 2011 9:47 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby Villeroy » Thu Apr 26, 2012 10:18 am

karolus wrote:Hi
Check in your Originaldocument →Tools→Options→OOocalc→Calculate [x]allow Regular Expressions in Formula

These Options are stored individually by Document !

Karo

and "allow Regex in Formula" is off in all documents that used to be Excel documents in a former life since Excel does not know any regexes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17243
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Extracting the numbers from a string of alphanumeric tex

Postby slsleng » Thu Apr 26, 2012 10:25 am

Thanks alot. It solved my problem. Indeed the original doc used to be Excel doc.
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby slsleng » Thu Apr 26, 2012 10:53 am

One more issue,

Eg : the string is ABC0456RK
The formula will extract 456 only but I need the zero as well. How can I include the zero in the result if the first character is zero?
Last edited by slsleng on Thu Apr 26, 2012 11:07 am, edited 1 time in total.
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby karolus » Thu Apr 26, 2012 11:06 am

Hi

slsleng wrote:One more issue,

Eg : the string is ABC0456RK
The formula will extract 456 only but I need the zero as well. How can I do it?


In Case my Formula delete *1 at the End of Formula, or delete VALUE( in JohnSunPensioners Formula.

Karo
AOO4, Libreoffice 4.2 on Linux Mint16
User avatar
karolus
Volunteer
 
Posts: 350
Joined: Sat Jul 02, 2011 9:47 am

Re: Extracting the numbers from a string of alphanumeric tex

Postby slsleng » Thu Apr 26, 2012 11:10 am

Thanks. My problem is solved.
OpenOffice 3.3
slsleng
 
Posts: 7
Joined: Thu Apr 26, 2012 5:18 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 28 guests