[Solved] Extract numbers from a string of alphanumeric text
[Solved] Extract numbers from a string of alphanumeric text
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.
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].
Reason: tagged [Solved].
OpenOffice 3.3
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Extracting the numbers from a string of alphanumeric tex
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 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...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Extracting the numbers from a string of alphanumeric tex
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.
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 2315 times
OpenOffice 3.3
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Extracting the numbers from a string of alphanumeric tex
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"
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"
- Attachments
-
- sample.ods
- (20.8 KiB) Downloaded 1510 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Extracting the numbers from a string of alphanumeric tex
Hi
Why not direct on Column C:
Karo
Why not direct on Column C:
Code: Select all
=MID(C10;SEARCH("[0-9]+";C10);SEARCH("[0-9][A-Z]";C10)-SEARCH("[0-9]+";C10)+1)*1
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Extracting the numbers from a string of alphanumeric tex
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.
I'm still new to open office and have not get use to it yet.
OpenOffice 3.3
Re: Extracting the numbers from a string of alphanumeric tex
Hi
Change the Celladdresses in the Formula to that with contents the String...
In your Sample.ods the Strings starts in C10.
Karo
Change the Celladdresses in the Formula to that with contents the String...
In your Sample.ods the Strings starts in C10.
Karo
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Extracting the numbers from a string of alphanumeric tex
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.
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.
OpenOffice 3.3
Re: Extracting the numbers from a string of alphanumeric tex
Hi
Check in your Originaldocument →Tools→Options→OOocalc→Calculate [x]allow Regular Expressions in Formula
These Options are stored individually by Document !
Karo
Check in your Originaldocument →Tools→Options→OOocalc→Calculate [x]allow Regular Expressions in Formula
These Options are stored individually by Document !
Karo
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Extracting the numbers from a string of alphanumeric tex
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.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
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: Extracting the numbers from a string of alphanumeric tex
Thanks alot. It solved my problem. Indeed the original doc used to be Excel doc.
OpenOffice 3.3
Re: Extracting the numbers from a string of alphanumeric tex
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?
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
Re: Extracting the numbers from a string of alphanumeric tex
Hi
Karo
In Case my Formula delete *1 at the End of Formula, or delete VALUE( in JohnSunPensioners Formula.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?
Karo
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Extracting the numbers from a string of alphanumeric tex
Thanks. My problem is solved.
OpenOffice 3.3
-
- Posts: 2
- Joined: Thu Aug 24, 2017 10:53 am
Extract numbers from a string of alphanumeric text
I'm very new to the vba coding so please help me out here.
I want to auto generate the emp code for the employees. The Emp code is a alpha numeric string - where i want to find the maximum and add +1 to it and create a new emp code. For eg:
Emp code
a001
a002
a111
a235
i want to get the next value as a236.
The logic that i have in mind is - Firstly i have to do right of the whole range and keep it stored in range itself than find the max of the range and store it to a variable1.
Secondly variable2 where i have to add 1 to the maximum number which is stored in variable1 & finally variable2 to fetch a new value as "a+variable2"
I want all this to happen in back end and should not effect, add or replace anything in my data and provide me the next unique value. PLEASE HELP
I do not have any code built yet just have tried the regex code i found on some forum but didnt work out.
I want to auto generate the emp code for the employees. The Emp code is a alpha numeric string - where i want to find the maximum and add +1 to it and create a new emp code. For eg:
Emp code
a001
a002
a111
a235
i want to get the next value as a236.
The logic that i have in mind is - Firstly i have to do right of the whole range and keep it stored in range itself than find the max of the range and store it to a variable1.
Secondly variable2 where i have to add 1 to the maximum number which is stored in variable1 & finally variable2 to fetch a new value as "a+variable2"
I want all this to happen in back end and should not effect, add or replace anything in my data and provide me the next unique value. PLEASE HELP
I do not have any code built yet just have tried the regex code i found on some forum but didnt work out.
windows 8 & microsoft office 2007
-
- Posts: 2
- Joined: Thu Aug 24, 2017 10:53 am
Re: Extract numbers from a string of alphanumeric text
Hi all, I did find a work around with the little knowledge that i have about vba. i tried by best to run the whole process in back end but was not successful, so allowed by data to change. But in the end i somehow have to get the data back into same format as before is where i'm stuck right now.
Below is the code :
Private Sub cmdsubmitdata_Click()
Dim rng As Range
Dim cl As Range
Dim i As Range
Dim c As Range
Windows("EMPDATA.xlsm").Activate
Sheets("EMP").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = WorksheetFunction.Max(Range("a:a")) + 1
Set rng = Range("B1", Range("B65536").End(xlUp))
lastnumber = Range("A65536").End(xlUp).Value
For Each cl In rng
cl.Value = Right(cl.Value, 3)
Next cl
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = WorksheetFunction.Max(Range("b:b")) + 1
Set rng = Range("B2", Range("B65536").End(xlUp))
lastnumber = Range("B65536").End(xlUp).Value
Set i = rng
For Each cl In rng
If Len(Cells(cl).Value) < 10 Then
cl.Value = "P00" & cl.Value
End If
Next cl
The last For loop is what I'm looking for now, the Emp code have changed to number and getting into a+variable2 is happening fine but emp code of a001 is populating as a1 and emp code of a060 as a60. It would be great help if anyone could enlighten me on this
Below is the code :
Private Sub cmdsubmitdata_Click()
Dim rng As Range
Dim cl As Range
Dim i As Range
Dim c As Range
Windows("EMPDATA.xlsm").Activate
Sheets("EMP").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = WorksheetFunction.Max(Range("a:a")) + 1
Set rng = Range("B1", Range("B65536").End(xlUp))
lastnumber = Range("A65536").End(xlUp).Value
For Each cl In rng
cl.Value = Right(cl.Value, 3)
Next cl
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = WorksheetFunction.Max(Range("b:b")) + 1
Set rng = Range("B2", Range("B65536").End(xlUp))
lastnumber = Range("B65536").End(xlUp).Value
Set i = rng
For Each cl In rng
If Len(Cells(cl).Value) < 10 Then
cl.Value = "P00" & cl.Value
End If
Next cl
The last For loop is what I'm looking for now, the Emp code have changed to number and getting into a+variable2 is happening fine but emp code of a001 is populating as a1 and emp code of a060 as a60. It would be great help if anyone could enlighten me on this
windows 8 & microsoft office 2007