Simple question(I think) - from a newbie

Ok, I have looked through here and I know cleaning up names is difficult. The thing is I have all the formulas I need to do it, and for my data it works every time. I was just wondering if there was a way to put them into a macro so that I don't have to use so many columns and cut and paste. Here is how everything is layed out.

Column "A" - Names
Column "B" - =TRIM(A2)
Column "C" - =IF(ISERROR(FIND(" ",A2,1)),A2,LEFT(A2,FIND(" ",A2,1)-1))
Column "D" - =IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),RIGHT(A2,LEN(A2)-FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND_
(" ",A2,FIND(" ",A2)+1)-0))
Column "E" - =CONCATENATE(D2," ",C2)
Then I copy and paste the values from "E" back into "A"

So I take raw names that have extra spaces and middle initials - I seperate ou tand get first and last names and then concatnate them together. What I am wondering is if I can use a macro that can do all of this in place so that I don't have to use so many columns and copy and paste these formulas. Since I don't know how many names are in each file I need it to do it all the way down column "A" untill the cell is blank.

I am sure this is simple, but my knowledge of macros doesn't go much past recording them.

If anybody can show me how to do something like this it would be greatly appreciated, as it would save me alot of time since I have to repeat this process on many workbooks.

Thanks for your time,

Emily Suskovich

With your data in A1, try this in B1. then just copy down

=LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)&"
"&RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND("*",SUBSTITUTE(TRIM(A1),"
","*",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Emily Suskovich" wrote in message
...
Simple question(I think) - from a newbie

Ok, I have looked through here and I know cleaning up names is difficult. The
thing is I have all the formulas I need to do it, and for my data it works every
time. I was just wondering if there was a way to put them into a macro so that
I don't have to use so many columns and cut and paste. Here is how everything
is layed out.

Column "A" - Names
Column "B" - =TRIM(A2)
Column "C" - =IF(ISERROR(FIND(" ",A2,1)),A2,LEFT(A2,FIND(" ",A2,1)-1))
Column "D" - =IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),RIGHT(A2,LEN(A2)-FIND("
",A2)),RIGHT(A2,LEN(A2)-FIND_
(" ",A2,FIND(" ",A2)+1)-0))
Column "E" - =CONCATENATE(D2," ",C2)
Then I copy and paste the values from "E" back into "A"

So I take raw names that have extra spaces and middle initials - I seperate ou
tand get first and last names and then concatnate them together. What I am
wondering is if I can use a macro that can do all of this in place so that I
don't have to use so many columns and copy and paste these formulas. Since I
don't know how many names are in each file I need it to do it all the way down
column "A" untill the cell is blank.

I am sure this is simple, but my knowledge of macros doesn't go much past
recording them.

If anybody can show me how to do something like this it would be greatly
appreciated, as it would save me alot of time since I have to repeat this
process on many workbooks.

Thanks for your time,

Emily Suskovich


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

If you are looking for a coding to preform this task try the code below


Sub Convert_Names(

Range("B1").Selec

Do Until ActiveCell.Offset(0, -1).Value = "
ActiveCell.Value =
"=RIGHT(TRIM(RC[-1]),LEN(TRIM(RC[-1]))-FIND(""*"",SUBSTITUTE(TRIM(RC[-1]),"" "",""*"",LEN(TRIM(RC[-1]))-LEN(SUBSTITUTE(TRIM(RC[-1]),"" "","""")))))& "" "" & LEFT(TRIM(RC[-1]),FIND("" "",TRIM(RC[-1])))
ActiveCell.Offset(1, 0).Selec
Loo
Columns("B:B").Selec
Selection.Cop
Columns("A:A").Selec
Selection.PasteSpecial Paste:=xlPasteValue
Columns("B:B").Selec
Application.CutCopyMode = Fals
Selection.ClearContent
Range("A1").Selec

End Su


----- Emily Suskovich wrote: ----

Simple question(I think) - from a newbi

Ok, I have looked through here and I know cleaning up names is difficult. The thing is I have all the formulas I need to do it, and for my data it works every time. I was just wondering if there was a way to put them into a macro so that I don't have to use so many columns and cut and paste. Here is how everything is layed out

Column "A" - Name
Column "B" - =TRIM(A2
Column "C" - =IF(ISERROR(FIND(" ",A2,1)),A2,LEFT(A2,FIND(" ",A2,1)-1)
Column "D" - =IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),RIGHT(A2,LEN(A2)-FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND
(" ",A2,FIND(" ",A2)+1)-0)
Column "E" - =CONCATENATE(D2," ",C2
Then I copy and paste the values from "E" back into "A

So I take raw names that have extra spaces and middle initials - I seperate ou tand get first and last names and then concatnate them together. What I am wondering is if I can use a macro that can do all of this in place so that I don't have to use so many columns and copy and paste these formulas. Since I don't know how many names are in each file I need it to do it all the way down column "A" untill the cell is blank.

I am sure this is simple, but my knowledge of macros doesn't go much past recording them

If anybody can show me how to do something like this it would be greatly appreciated, as it would save me alot of time since I have to repeat this process on many workbooks

Thanks for your time

Emily Suskovich

Ok, I got how that works and it helps alot, I do have one last question though, if at all possible, how hard is it to change this so that you get only the first letter of the last name. I assume this is possible, but have no idea how to do it.

Thanks for any help you can give me

Emily