Code For Selecting Part Of A Field 1

[iain2003]

I am searching for some code that will allow me to strip a single name field into title initial surname fields and also code that will allow me to extract domain information from email addresses, e.g taking ntlworld.com from iain@ntlworld.com. any ideas?

 

[TudorSmith]

The first part could be tricky (Title, Initial, Surname) unless there is cinsitancy in the original value i.e. there will always be a value like "Mr. J. Bloggs", but what if there are no dots, or two initials etc.....please define your values and I'll try and help.

To strip out the domain from an email address try this:

    Dim intIde As Integer
    Dim strDomain As String

    'Get the position of the @

    intIde = InStr(1, "MyEmail@MyDomain.com", "@")

    'Strip out everything preceeding the @

    strDomain = Right("MyEmail@MyDomain.com", Len("MyEmail@MyDomain.com") - intIde)

birklea birklea ~©¿©~ <><
I know what I know...don't presume that I know what I don't! Smithism!!!

 

[iain2003]

Thanks Birklea,

Worked a treat!
The constants for a name split function would always be the space between each part of the name, eg, Mr Joe Bloggs.
I considered using a select case for any variables in the name itself, eg Mr, Mrs, Dr, Miss etc. in the first name case I would like either a full first name to be displayed or in the case of an initial just return that eg,

If the name has just an 1 character then return that character Else
return the entire first name.

If this does not make any sense please feel free to ignore.

Regards

Iain

 

[TudorSmith]

Hi Iain...sorry, I've been on holiday so I couldn't respond any earlier.

Here's a function (which of course can be amended to suit your purposes.

Function StripOutName(strName As String)
    '=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    ' This function will strip out Title, Forename & Surname
    ' Using a space as the delimiter
    ' e.g. Mr Joe Bloggs will be passed in as the strName variable
    ' The function will return the following:
    '   strTitle = "Mr"
    '   strForeName = "Joe"
    '   strSurname = "Bloggs"
    '=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    
    Dim strTitle, strForename, strSurname As String
    
    'Get the Title
    '=-=-=-=-=-=-=
    Do Until Left(strName, 1) = " "
        strTitle = strTitle & Left(strName, 1)
        strName = Right(strName, Len(strName) - 1)
    Loop
    strName = Right(strName, Len(strName) - 1)
    
    'Get the Forename
    '=-=-=-=-=-=-=-=-
    Do Until Left(strName, 1) = " "
        strForename = strForename & Left(strName, 1)
        strName = Right(strName, Len(strName) - 1)
    Loop
    strName = Right(strName, Len(strName) - 1)
    
    'Whatever is left...it's the Surname
    '=-=-=-=-=-=-=-=
    strSurname = strName
    
    MsgBox "Title: " & strTitle & " Forename: " & strForename & " Surname: " & strSurname
End Function

When you pass in &quot;Mr Joe Bloggs&quot;, this function will return the value split into three parts (I have stored each of the three parts in their own variables)

It's a bit convoluted, but it does work. give it a go!

Tudor (birklea) birklea ~©¿©~ <><
I know what I know...don't presume that I know what I don't! Smithism!!!

 

[tonyireland]

if you're doing this a lot there is an add-in that is supposed to do this. I tried the demo once and it seemed OK. It costs of course

http://www.infoplan.com.au/splitter/