Extract a specific length of numbers from a string

Recently we have been asked to provide a solution for a client who was faced with potentially thousands of cells in a range where only a specific reference number within an alphanumeric sequence was required to be extracted. This was a fixed length of six numbers but this sequence could be anywhere in the string.

An example of the string is:

Thank you for your order|artyer6v7t8uit7567tbguij|Mr.Smith|545521|23/08/2014

The result was:

545521

The reference number that was the important part of this information needed to be identified, extracted and moved to another cell on the same row. Our example here shows you the UDF to facilitate identifying the 6 digit number in this cell or sequence.

Once the actual number has been identified, it is extracted with the UDF and can then be manipulated as required ie put in a new cell on the same row or manipulated to be placed elsewhere in a workbook or database system.

Feel free to ask us a question or leave a comment below.



Function SixDigitNo(s As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "(?:^|\D)(\d{6})(?!\d)"
If .Test(s) Then SixDigitNo = .Execute(s)(0).SubMatches(0)
End With
End Function






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *