Hi,

I just realised what the problem was.. the thing is the blank cells look
blank but actually contain some sort of formatting (that we cant see in cell)
i just wanted to see if this was the issue and when i did Edit-clear format
and then ran the macro the macro worked. so how can i tell the macro to clear
the formats of the cells first and then do the replacing? because without
having cleared the format the macro doesnt seem to work even though the cells
are blank, didnt think formatting would get in the way.

if you know how i can put it in the code to clear format then i wont need to
email you my file.

thanks so much.

"Gemz" wrote:

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.