Full Version: Suggestion for another macro.
From: Shaddy [#2]
5 Sep 2006
To: logojohn [#1] 6 Sep 2006
It's possible to make custom macros for all that stuff, but every spreadsheet you might get might be a tad different. It might be better to learn how to manipulate the spreadsheet instead, then use the more general macro.
This can be done in excel easily, if you have excel.
Let's say you have 3 columns of data, fist name, last name and title. If you wanted to combine the first 2 columns, I would
1) insert new column after names, before title.
2) add the collowing line to the first row of the new column.
=A1 &" " & B1
(that says, add column a and b, with a space between, if you wanted last name, first... it would look like
=B1 &", " & A1
3) hold mouse over lower right hand corner of that box, click and drag it down until all the rows are filled.
4) select the data you want, then copy to a different sheet (edit, paste special, values... that avoids the referance errors)
5) copy new sheet then run Carls macro.
It might seem daunting, but I actually could do that all in just a minute or so.
Shaddy
From: Harvey only (HARVEY-ONLY) [#3]
5 Sep 2006
To: Shaddy [#2] 5 Sep 2006
Good suggestion. For under 100 names it should take less than 30 seconds total.
Also as you said, you can get the right format every time.
From: logojohn [#4]
6 Sep 2006
To: Shaddy [#2] 6 Sep 2006
It worked out great making a new column with first and last name.
I assume there is no magic trick to convert a
Lastname, First name listed in one column into
Firstname Lastname
.
From: Carl (CSEWELL) [#5]
6 Sep 2006
To: logojohn [#4] 8 Sep 2006
Why, there just happens to be!!!
Place the "Lastname, Firstname" in A1. Then place the first line below in B1 and the next line in C1, and see what develops:
=LEFT(A1,FINDB(",",A1,1)-1)
=RIGHT(A1,LEN(A1)-FINDB(",",A1,1)-1)
And then copy the functions to the cells underneath.
WARNING! Hasn't been fully debugged, but what do you expect for a few seconds of work?
From: Shaddy [#6]
6 Sep 2006
To: logojohn [#4] 8 Sep 2006
Although I would use code similar to Carls, there's also a wizard in Excel to do it, called "Text to Columns" (under the Data menu). Follow the directions, in this case using a comma as the delimiter.
Shaddy