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.