A way to insert title in excel 1

[gizmo1973]

Hi all,

I have some data with peoples names in.
I want to put their title in like so

Fred Bloggs becomes Mr Fred Bloggs
Fredrica Bloggs becomes Ms Fredrica Bloggs
Georgina Bloggs becomes Mrs Georgina Bloggs

I have tried to write a macro for this but can't get it to work, it seems to want to only change the original data everytime (I'm hopless with macros so won't embaress myself by posting the code)
It's all down to another cell which contains the correct title but only an initial which is why I can't replace all.
I want to click in a cell and then run the macro to tell it to insert Mr or Ms or Miss or Mrs before the text in that cell.
is this possible?

Regards, Phil

"If in doubt, hit it with an end user!

 

[jonsi]

does the other cell contain ONLY their correct salutation or do you have Mr F Bloggs, Ms F Bloggs, Mrs G Bloggs.

If it's only their salutation and it's on the same line as their full name then you should be able to do this easily.

Failing that you're going to have to indicate the correct salutation alongside their name somehow (1=Mr,2=Mrs,3=Ms, 4=Dr,5=Rev etc.) seems most logical



hwyl
Jonsi B-)
"We live in a society exquisitely dependent on science & technology, in which hardly anyone knows anything about science & technology" - Carl Sagan

 

[sdraper]

Sub MrMissMrs()
    ' this will add Mr,Ms or Mrs to the active cell
    ' based on criteria in the cell one column to
    ' the right of the active cell
    Dim strCriteria As String
    'adjust offset to your criteria cell address
    strCriteria = ActiveCell.Offset(0, 1).Value
    Select Case strCriteria
        ' adjust 1,2 & 3 to your criteria values
        Case 1 'mister
            ActiveCell.Value = "Mr. " & ActiveCell.Value
        Case 2 'miss
            ActiveCell.Value = "Ms. " & ActiveCell.Value
        Case 3 'misses
            ActiveCell.Value = "Mrs. " & ActiveCell.Value
    End Select
End Sub

This should do the trick

Sam

 

[gizmo1973]

No, it can contain anything from their full name to nothing.
It's only a clue not a definite answer.
I really want a point and click macro
i.e. I'm in cell a5 with "F Bloggs" in it and in b5 it says "Mr F bloggs"
I want to run a macro from a5 which will ask which title I want from the list you gave and then inserts it into that cell or range of cells if selected.
I know what I want but have no idea how to write it

Regards, Phil

"If in doubt, hit it with an end user!

 

[gizmo1973]

Thanks Sam, I must have been wrting my reply to Jonsi as you posted that code, so as you can see it won't really work
Thanks for he help anyway.
it will come in handy when I gte my database sorted!

Regards, Phil

"If in doubt, hit it with an end user!

 

[sdraper]

just change
this line:
strCriteria = ActiveCell.Offset(0, 1).Value
to this:
strCriteria = InputBox("enter 1 for Mr, 2 for Mrs and 3 for Mrs", "Choose a prefix", "1")


Sam

 

[sdraper]

you should also add:

strCriteria = trim(strCriteria)
    If IsNumeric(strCriteria) Then
        If strCriteria < 1 Or strCriteria > 3 Then
            MsgBox "numbers 1,2 or 3 only!"
            Exit Sub
        End If
    Else
        MsgBox "numbers 1,2 or 3 only"
        Exit Sub
    End If

right before the line:
Select Case strCriteria
to trap bad user input.


Sam

 

[jonsi]

a simple solution would be to have a Data Validation Cell alongside each entry to allow you to select the title

eg F Bloggs in B2
Cell C2 has Data>Validation> List of Mr,Mrs,Miss,Ms, Rev, Dr etc
Cell D2 has formula =C2&" "&B2

D2 now gives Mr F Bloggs

 

[gizmo1973]

Sam this is working fantastically.
One last question, how do I add a keyboard shortcut to it?

tried typing keyboard shortcut = ctrl & t before the code but it won't accept it


Regards, Phil

"If in doubt, hit it with an end user!

 

[sdraper]

go to tools>macros and highlight the macro in question.
Click on the options button and you can choose a shortcut.
I am Excel 2000 so your version may differ

Sam

 

[gizmo1973]

Spot on!

Regards, Phil

"If in doubt, hit it with an end user!