The code did not work, the following line was highlighted in red:

ElseIf Len(c.Offset(0, -2).Text & c.Offset(0, -1).Text & c.Text) = 0
Then

i tried to move it back or forward a little incase it was messing with the
syntax but it didnt work.

the other code given by Joel didnt work either, i have emailed him too.

Please help! thanks.

"Ron Rosenfeld" wrote:

On Wed, 23 Jan 2008 09:13:01 -0800, Zak wrote:

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.


==========================
Option Explicit
Sub fText()
Dim c As Range
Dim l As Long, a As Long
For Each c In Range("C1:C10")
l = InStr(1, c.Value, "/")
a = InStr(1, c.Value, "@")
If l 0 Then
c.Value = Left(c.Value, l - 1)
ElseIf a 0 Then
c.Value = Application.WorksheetFunction.Proper _
(Replace(Left(c.Value, a - 1), ".", " "))
ElseIf Len(c.Text) = 0 And Len(c.Offset(0, -2).Text) 0 And _
Len(c.Offset(0, -1)) 0 Then
c.Value = c.Offset(0, -2).Value _
& " " & c.Offset(0, -1).Value
ElseIf Len(c.Offset(0, -2).Text & c.Offset(0, -1).Text & c.Text) = 0
Then
c.Value = "Vacancy"
End If

'Now check column E
If c.Offset(0, 2).Text Like "*.*" Then
c.Offset(0, 2).Value = Application.WorksheetFunction.Proper _
(Replace(c.Offset(0, 2).Text, ".", " "))
End If
Next c
End Sub
=================================
--ron