Brilliant!!
It works fine, yes i did put the code in a module.

Thanks a lot for all your help.


"Rick Rothstein (MVP - VB)" wrote:

When I put the code I posted in the code window for the worksheet you want
to apply it to, then the code works fine without having to clear any
formatting. I'm guessing you are running the code from a different code
window (perhaps from a Module?) and that is why it is not working. Here is a
revision to my code which will run against the active worksheet from any
code window (without needing to clear the formats); so make sure you are on
the worksheet you want to fix the names on before you run it...

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
With ActiveSheet
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 With
End Sub

Rick


"Gemz" wrote in message
...
i Hi,

i just sent you a sample spreadsheet on ur email, i didnt know how to
check
the formatting coz when i checked it didnt show anything.

thanks alot



"Rick Rothstein (MVP - VB)" wrote:

Just so we can duplicate your conditions... what was the formatting you
had
in the cells?

Rick


"Gemz" wrote in message
...
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.