UDF to locate first letter in string

In the process of data manipulation, data cleansing for instance it is a regular occurrence to identify a structure of the string in a cell for a postcode or telephone number for instance, or to facilitate splitting a cells contents up by numbers, letters etc.

This example UDF is designed to be pasted into the VB Module (ALT + F11) and be used as an everyday formual within Excel to identify the location of the the first letter in a string in a cell.

Let’s say cell A1 has the following value: 012345998D-45999

This could be a set of data that has become merged in a web extract, or has just been stored incorrectly and is required to be cleansed and turned into valuable and usable data. Using the formula in any cell to identify the position of the first letter, referencing A1 you would type:


=FirstLetter(A1)


Once you knwo the position of the first letter you can use Excels other useful string commands such as left, right, mid, len etc – all these are described in their own part of the blog.

Feel free to ask us a question or leave a comment below and we’ll get back to you.



Public Function FirstLetter(Sin As String) As String
Dim i As Long, CH As String
FirstLetter = ""
For i = 1 To Len(Sin)
If Mid(Sin, i, 1) Like "[A-Z,a-z]" Then
FirstLetter = Mid(Sin, i, 1)
Exit Function
End If
Next i
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 *