[Solved] Extract numbers from a string of alphanumeric text

[slsleng]

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.

[JohnSUN-Pensioner]

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.

[slsleng]

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.

[JohnSUN-Pensioner]

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"

[karolus]

Hi
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

Karo

[slsleng]

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.

[karolus]

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

Karo

[slsleng]

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.

[karolus]

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

These Options are stored individually by Document !

Karo

[Villeroy]

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.

[slsleng]

Thanks alot. It solved my problem. Indeed the original doc used to be Excel doc.

[slsleng]

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?

[karolus]

Hi

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

[slsleng]

Thanks. My problem is solved.

[jigarrathod]

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.

[jigarrathod]

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

[imacanuck]

Hi
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

Karo

Hello, although this post is quite old and SOLVED, it relates to my problem. Not sure if I should be posting this here or starting a new one?

Anyway, I need to be able to extract some additional characters along with the numbers. The code karolus provided works well, I just need it to also extract a number such as 3.5 which is written as 3-1/2 in the text string.

Any help would be great.
Thanks.

[imacanuck]

I should add, extracting the "3-1/2" without converting it to 3.5 would be preferred.

Thanks.

[Villeroy]

LibreOffice only: =REGEX(A1;"([^[:alpha:]])+")

[imacanuck]

Thanks Villeroy, REGEX in LibraOffice worked great for a project I was working on a while back once it was shown to me, but is there a method that would work in OpenOffice?

[FJCC]

I would break this into three columns. Say your text is in A1. In C1, find the first non-alpha character with

Code: Select all

=SEARCH("[^[:alpha:]]";A1)

In D1, find the first alpha character that is after the non-alpha character

Code: Select all

=SEARCH("[:alpha:]";A1;C1)

In E1, pull the text between those positions

Code: Select all

=MID(A1;C1; D1-C1)

That will fail if the numbers are at the end of the text.

[imacanuck]

FJCC, your suggestion works perfectly, thank you!

[Lupp]

...
I just need it to also extract a number such as 3.5 which is written as 3-1/2 in the text string.

(Spoken aside:)
If someone claims that they are transmitting a value of 3.5 as "3-1/2" instead of "3+1/2", I would question their understanding and not trust the information.

[imacanuck]

...
I just need it to also extract a number such as 3.5 which is written as 3-1/2 in the text string.

(Spoken aside:)
If someone claims that they are transmitting a value of 3.5 as "3-1/2" instead of "3+1/2", I would question their understanding and not trust the information.

Just a note Lupp... it is common practice in the steel industry here in Canada to use the following format when using the imperial measurement system to show the length of an item as this: 1'-9-13/16". I agree that it is not the best method but it is the world I live in!

[Lupp]

... when using the imperial measurement system ...

That's the fundamental mistake.
It's completed in a funny way by misusing the minus sign as a separator where it easily can be misunderstood as an arithmetic operator.

BTW:
1. Canada joined the metre convention (negotiated and signed 1875 by many countries) in 1907. There should have been time enough to introduce it also to the Canadian steel industry.
2. The "imperial" units were introduced (probably as a part of British imperialism) in 1824 when a better proposal already had been made. (Original Metre in France 1799)
3. Even GB also joined the metre convention already in 1884.

(Germany had introduced the metre by law already in 1872 and joined the convention in 1875.)

[imacanuck]

I do not disagree Lupp!
I'd prefer if we used the metric system across the board here, it's is easier by far but there are those who are too stubborn to adopt the easier system. It is even more frustrating when there are projects that has one engineering group using one system and another group will use the other! It makes no sense to me.

[Alex1]

It's completed in a funny way by misusing the minus sign as a separator where it easily can be misunderstood as an arithmetic operator.

We do the same with dates: 13-04-2024.

[Lupp]

We do the same with dates: 13-04-2024.

Thanks for the hint. I actually had that case also in mind, but ...
1. I wouldn't accept your example as a date.
2. The date representation as exemplified in the rectified way "2024-04-13" is explicitly standardised in ISO 8601, and the very strict syntax (4 digits, "-", 2 digits, "-", 2 digits) helps to minimize the chance of misinterpretations. In addition I wouldn't start to critisize ISO here. Getting acceptance for standardization at all is difficult enough anyway. Yet there were more mistakes than this one in their specifications. (Alternatives concerning the decimal separator, impractical rule concerning "thousands separators", usage of the "M" in two very different meanings in the same context of date-time ...)

But above all, I must apologize for not mentioning that in Germany, too, customs measurements ("Zoll" in place of "inch") have always been used in certain cases. You see: Introduce bad practise once, and you won't get rid of it in centuries.