Excel Help Please !
 

Hi, I am a newbie to excel and would like advice on how to acheive the
following:
We have a spreadsheet complete with a user name ie. Joe.Bloggs, this
name is copied across 4 cols
Joe.Bloggs Joe.Bloggs Joe.Bloggs Joe.Bloggs . I would like in essence 3
macro's (or 1 big one), which will look at col 2 and add a string of
text to the end ie. , Col 3 will strip of the
surname name ie. Joe and col 4 will strip of the surname "Bloggs". As
there are many entries for each row I would like all macros to
continually check the rows until an empty cell is found. I hope this
makes sense and thank you for your time. John.

Excel Help Please !
 

Forget about macros. I will assume the names are in column A starting in row
1.
IN B1 enter "
In C1 enter =LEFT(A1,FIND(".",A1)-1)
In D1 enter =MID(A1,FIND(".",A1)+1,255)
Select B1:D1 and copy down the to last row - best done by double clicking
the fill handle of D1 - the small solid square in lower right cornet
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John H" wrote in message
ups.com...
Hi, I am a newbie to excel and would like advice on how to acheive the
following:
We have a spreadsheet complete with a user name ie. Joe.Bloggs, this
name is copied across 4 cols
Joe.Bloggs Joe.Bloggs Joe.Bloggs Joe.Bloggs . I would like in essence 3
macro's (or 1 big one), which will look at col 2 and add a string of
text to the end ie. , Col 3 will strip of the
surname name ie. Joe and col 4 will strip of the surname "Bloggs". As
there are many entries for each row I would like all macros to
continually check the rows until an empty cell is found. I hope this
makes sense and thank you for your time. John.

Excel Help Please !
 

In C1 enter:
=B1 & "
in D1 enter:
=LEFT(B1,FIND(".",B1)-1)
In E1 enter:
=RIGHT(B1,LEN(B1)-FIND(".",B1))


and copy down

--
Gary's Student
gsnu200701


"John H" wrote:

Hi, I am a newbie to excel and would like advice on how to acheive the
following:
We have a spreadsheet complete with a user name ie. Joe.Bloggs, this
name is copied across 4 cols
Joe.Bloggs Joe.Bloggs Joe.Bloggs Joe.Bloggs . I would like in essence 3
macro's (or 1 big one), which will look at col 2 and add a string of
text to the end ie. , Col 3 will strip of the
surname name ie. Joe and col 4 will strip of the surname "Bloggs". As
there are many entries for each row I would like all macros to
continually check the rows until an empty cell is found. I hope this
makes sense and thank you for your time. John.

Excel Help Please !
 

Sub columnschange()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
c.Offset(, 1) = c & "
c.Offset(, 2) = Left(c, InStr(c, ".") - 1)
c.Offset(, 3) = Right(c, InStr(c, ".") + 2)
Next
End Sub

--
Don Guillett
SalesAid Software

"John H" wrote in message
ups.com...
Hi, I am a newbie to excel and would like advice on how to acheive the
following:
We have a spreadsheet complete with a user name ie. Joe.Bloggs, this
name is copied across 4 cols
Joe.Bloggs Joe.Bloggs Joe.Bloggs Joe.Bloggs . I would like in essence 3
macro's (or 1 big one), which will look at col 2 and add a string of
text to the end ie. , Col 3 will strip of the
surname name ie. Joe and col 4 will strip of the surname "Bloggs". As
there are many entries for each row I would like all macros to
continually check the rows until an empty cell is found. I hope this
makes sense and thank you for your time. John.

Excel Help Please !
 

Thankyou all for your valuable help and very speedy response !.

John.

Don Guillett wrote:
Sub columnschange()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
c.Offset(, 1) = c & "
c.Offset(, 2) = Left(c, InStr(c, ".") - 1)
c.Offset(, 3) = Right(c, InStr(c, ".") + 2)
Next
End Sub

--
Don Guillett
SalesAid Software

"John H" wrote in message
ups.com...
Hi, I am a newbie to excel and would like advice on how to acheive the
following:
We have a spreadsheet complete with a user name ie. Joe.Bloggs, this
name is copied across 4 cols
Joe.Bloggs Joe.Bloggs Joe.Bloggs Joe.Bloggs . I would like in essence 3
macro's (or 1 big one), which will look at col 2 and add a string of
text to the end ie. , Col 3 will strip of the
surname name ie. Joe and col 4 will strip of the surname "Bloggs". As
there are many entries for each row I would like all macros to
continually check the rows until an empty cell is found. I hope this
makes sense and thank you for your time. John.

Excel Help Please !
 

Glad to help

--
Don Guillett
SalesAid Software

"John H" wrote in message
ps.com...
Thankyou all for your valuable help and very speedy response !.

John.

Don Guillett wrote:
Sub columnschange()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
c.Offset(, 1) = c & "
c.Offset(, 2) = Left(c, InStr(c, ".") - 1)
c.Offset(, 3) = Right(c, InStr(c, ".") + 2)
Next
End Sub

--
Don Guillett
SalesAid Software

"John H" wrote in message
ups.com...
Hi, I am a newbie to excel and would like advice on how to acheive the
following:
We have a spreadsheet complete with a user name ie. Joe.Bloggs, this
name is copied across 4 cols
Joe.Bloggs Joe.Bloggs Joe.Bloggs Joe.Bloggs . I would like in essence 3
macro's (or 1 big one), which will look at col 2 and add a string of
text to the end ie. , Col 3 will strip of the
surname name ie. Joe and col 4 will strip of the surname "Bloggs". As
there are many entries for each row I would like all macros to
continually check the rows until an empty cell is found. I hope this
makes sense and thank you for your time. John.