When you say "blank", you do mean with **nothing**, not even a blank space,
in the the cell, correct? You are saying my code doesn't do steps 3 and 4 at
all, anywhere on your sheet? As far as I can tell from my own testing, the
code I posted **does** do both steps number 3 and 4 correctly.

Can you post a copy of your spreadsheet (the one where you say it doesn't
work) on a webpage so I, and others, can test our code against your actual
data conditions? If not, then email me a copy of your spreadsheet (remove
the NOSPAM stuff from my email address) so I can try it out against me own
code. Please reply to this message with either way you proceed (so if you
are emailing it to me, I'll know to look out for it).

Rick


"Gemz" wrote in message
...
thanks so much for the quick reply but this code only works partially..

-it works fine for most steps(conditions) but doesnt do anything with:

3-if a cell in column c is blank and the corresponding cells in columns A
&
B arent (should contain first name and second name in A & B)then A & B
must
be concatenated with a space (to make first name and second name come
together) and then instert into the coresponding cell in column C.
4- if A,B & C are all blank then the the word 'Vacancy' should be inserted
in column C.

weirdly the above to steps in the code dont work. why?

thanks alot.

"Rick Rothstein (MVP - VB)" wrote:

Give this a try...

Sub FixNames()
Dim LastColumnInAB As Long
Dim LastColumnInC As Long
Dim LastColumnInE As Long
Dim Position As Long
Dim MaxCol As Long
Dim Cel As Range
LastColumnInAB = Cells(Rows.Count, "A").End(xlUp).Row
LastColumnInC = Cells(Rows.Count, "C").End(xlUp).Row
LastColumnInE = Cells(Rows.Count, "E").End(xlUp).Row
If LastColumnInAB LastColumnInC Then
MaxCol = LastColumnInAB
Else
MaxCol = LastColumnInC
End If
For Each Cel In Range("C1:C" & MaxCol)
If Len(Cel.Value) = 0 Then
Cel.Value = Trim$(Cells(Cel.Row, "A") & " " & Cells(Cel.Row, "B"))
If Len(Cel.Value) = 0 Then Cel.Value = "Vacancy"
Else
Position = InStr(Cel.Value, "/")
If Position = 0 Then Position = InStr(Cel.Value, "@")
If Position 0 Then Cel.Value = StrConv(Replace(Left$(Cel.Value, _
Position - 1), ".", " "), vbProperCase)
End If
Next
For Each Cel In Range("E1:E" & LastColumnInE)
Cel.Value = StrConv(Replace(Cel.Value, ".", " "), vbProperCase)
Next
End Sub

Rick


"Zak" wrote in message
...
I have a few formatting requirements for my spreadsheet and i was
wondering
if you can help:

1-if name in column C appears as: Joe Bloggs/UK/SC/CSC then everything
from
the first / onwards needs to be deleted.
2- if in column C something appears as: then
everything
from @ onwards must be deleted, the dot replaced with a space and the
1st
letter of each name to be changed to capital - so should look like: Joe
Bloggs.
3-if a cell in column c is blank and the corresponding cells in columns
A
&
B arent (should contain first name and second name in A & B)then A & B
must
be concatenated with a space (to make first name and second name come
together) and then instert into the coresponding cell in column C.
4- if A,B & C are all blank then the the word 'Vacancy' should be
inserted
in column C
5- if anything in column E is as: joe.bloggs then this should be
changed
to
replace dot with space and again as above make the j a capital J and
the h
a
capital H.

i hope you can offer some help.

thanks alot.