Remove Initial From Name

[andy_dyer]

Hi,

I have a list of names in the format:

Mr A Smith
Mrs A J Jones

I would like to be able to remove all trace of an initial and be left with just:

Mr Smith
Mrs Jones

I have used a query before to identify the initial from a first name for me to use elsewhere in a database but never before have I tried to remove it.

The code I used to isolate it from the First Name was:

Initial: Left([Name],1)

Any ideas??

 

[Mile-O]

Have a look at this function: click here

Ensure the delimiter is a space and then use it to concatenate the parts you need.

 

[andy_dyer]

Thanks Mile-O-Phile!

That's an awful lot of code to find an initial!!

So exactly how can i use this to remove the initial for the purposes of a mail merge?

e.g. I am using for the address "Mr A Smith" but for the salutation I will be using "Dear Mr Smith"

I am presuming that I may need to create a hidden text box that will take the name as it is and make the changes and then I can reference this in my word document...

Good idea in theory...

How can I actually do it??

 

[Fornatian]

try this:

firstly include this function in a public module

Function ReverseString(linea As String) As String
'This procedure produces a reversal of
'an inputted string.

Dim result As String, i As Integer, n As Integer

n = Len(linea)
result = ""
For i = n To 1 Step -1
result = result & Mid(linea, i, 1)
Next i
ReverseString = result

End Function

Then include this in your recordsource query

Greeting: Left([aName],InStr(1,[aName]," ",0)) & ReverseString(Left(ReverseString([aName]),(InStr(1,ReverseString([aName])," ",0))-1))

This assumes that the name is always the string after the last space and the title is always the string before the first space.

 

[andy_dyer]

I have no idea how you did that Fornation, but it's worked!!!!

Thank you so much!!!

 

[Fornatian]

Let's assume the string is "Mr A Smith"

Left([aName],InStr(1,[aName]," ",0))

= "Mr ", because it finds the first space and returns everything before it.

ReverseString(Left(ReverseString([aName]),(InStr(1
,ReverseString([aName])," ",0))-1))

Firstly we reverse the string so we get:

"htimS A rM"

then find the first space and pull everything before it
"htimS"

then reverse it to give the last name:

"Smith"

put them both together and you get:

"Mr Smith"

 

[andy_dyer]

Clever, very clever!!!

 

[namliam]

Then what about

Miss A Smith Jones

or

Mr T Fox Worthy

and stuff like that....

Regards?!

 

[Fornatian]

This assumes that the name is always the string after the last space and the title is always the string before the first space.

All functions work within limitations. However if you insist, make a reference to Microsoft Excel 8.0 object library in your VBA references then add this function:

Function GetName(strName As String)
On Error GoTo my_err

Dim strTitle As String
strTitle = Left(strName, InStr(1, strName, " ", 0))


GetName = strTitle & Mid(strName, WorksheetFunction.Search(" ? ", strName, 1) + 3)

exit_func:

Exit Function

my_err:
If Err.Number = 1004 Then
GetName = strName
Else
MsgBox Err.Number & Err.Description
End If
End Function

Again this has it's downfalls as it returns Mr B Smith for "Mr A B Smith", which is why it is always best to separate these things into separate fields initially as it is easier to put them together than pull them apart.

Andy seems satisfied with my original response anyway
What's the solution Namliam?

 

[namliam]

I must admit i only read part of the post and was playing the devils advocat