Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11480

Re: Searching a string

Show all headers | View raw

On Tuesday, 19 January 2016 16:04:43 UTC, internet...@foobox.com  wrote:
> I am wishing to import records from a spreadsheet.  One important field contains a person's name.  The surname is always first, followed by first names or initials.
> 
> e.g
> 
> Smith J
> Smith John
> Smith J, H
> Smith John H
> Smith, J
> Smith,J
> Smith,John
> Smith; John
> Smithers K
> Smitherson,L
> Smithson; Jack
> 
> I am wanting to extract the surname.  
> 
> To do this I need a function that will find the first space, comma or semi-colon (and maybe other characters)  and then use the left() function to extract the surname.
> 
> My problem is the first search function.  Instr() will find the first occurrence of a character but I want to find the first occurrence of any character in a given set e.g. [ ,;]
> 
> It looks as if I need a regular expression but it many years since I used them; and I am not sure how to use them in VBA.
> 
> Any help would be welcome.

Create a query, First column is ExcelSurname, second column is 
RealSurName: GetSurname([ExcelSurName]) 

Then a bit of code

Option Compare Database
Option Explicit

Function GetSurname(StrIn As String) As String

    Dim i As Integer
    
    For i = 1 To Len(StrIn)
        If Asc(Mid(StrIn, i, 1)) < 65 Or Asc(Mid(StrIn, i, 1)) > 122 Or (Asc(Mid(StrIn, i, 1)) > 90 And Asc(Mid(StrIn, i, 1)) < 97) Then
            Mid(StrIn, i, 1) = " "
        End If
    Next i
    
    
    GetSurname = Left(StrIn, InStr(StrIn, " "))
    Debug.Print GetSurname
    
End Function

Basically it substitutes a space for any characters outside the 26 upper and lower case characters a-z and A-Z, then outputs the string up to the first space

Phil

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Searching a string internet.shopping@foobox.com - 2016-01-19 08:04 -0800
  Re: Searching a string pstanton54@gmail.com - 2016-01-19 10:03 -0800
    Re: Searching a string internet.shopping@foobox.com - 2016-01-19 11:32 -0800
      Re: Searching a string Mike P <mikewpayne@tiscali.co.uk> - 2016-01-19 15:00 -0800
  Re: Searching a string pstanton54@gmail.com - 2016-01-19 15:42 -0800

csiph-web