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.