Macro to combine several worksheet formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
Simple question(I think) - from a newbie

Ok, I have looked through here and I know cleaning up names is difficult. The
Click to expand...
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
Click to expand...
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
Click to expand...
appreciated, as it would save me alot of time since I have to repeat this
process on many workbooks.
 
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
 
Back
Top