Full Version: Suggestion for another macro.
From: logojohn [#1]
5 Sep 2006
To: ALL
Before I needed the second tabbed column put on a new line under the first column Such as Name and Title.
When I used it the new problem showed up.
The people put the first name in one column and the last name in a second column.
When using the replace tabs macro it removes the tab space but puts the last name on another line below it.
It shouldn't be too hard to make one that just removes the tab space and leaves the second column on the same line as the first with 1 space between.
Or maybe even put a choice built into the first replace macro.
No big deal on this job as it was only 45 names.
Thanks
.
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