| 0 |
Subject: A couple quick EXCEL questions
Posted by: Tosh
- Sustainer [057721710] Thu, Nov 18, 2004, 18:59
Thanks in advance.
1. Suppose I have a column of names, all with the individual cell format of Fluid, Recycled Spinal. I can use the 'Text to columns' feature to separate the name into two (or three) distinct columns (last, first, middle). Is it possible to take these three new columns, and re-combine them back into one? I am looking to take a list of names, remove the middle name, and put the name back into one cell.
2. I have a column of names that are all capitalized. I want to un-capitalize everything except the first letter of the names.
Thanks again!
|
| 1 | Tosh
Sustainer
ID: 057721710
Thu, Nov 18, 2004, 19:00
|
Addendum to question 1-
I want the new cell to be in the format of Recycled Fluid.
|
| 2 | rockafellerskank
Dude
ID: 27652109
Thu, Nov 18, 2004, 19:17
|
Tosh- Part 1 can be done witha function called Concatenate. Go to functions/ ALL/ and scroll down. You will get a wizard that you can click into to define what columns you want to "put back together"
You may (depending on what you want for appearance) include a , or a space to prevent a run-on concatenation!
|
| 3 | Tosh
Sustainer
ID: 057721710
Thu, Nov 18, 2004, 19:41
|
Thanks for that tip. I would have never found that myself. Hopefully the wizard will be friendly to me!
|
| 4 | Guru
ID: 330592710
Thu, Nov 18, 2004, 21:48
|
You can always combine two or more strings by using the & operator.
For example, if cell A1="Fluid" and A2="Recycled", then the following formula:
=A2&" "&A1
will result in the value "Recycled Fluid"
|
| 5 | RecycledSpinalFluid
Dude
ID: 204401122
Thu, Nov 18, 2004, 21:53
|
Step 2:
=Proper(A1)
|
| 6 | allhair allstars
Sustainer
ID: 50902421
Thu, Nov 18, 2004, 21:53
|
Tosh,
As far as the capitalization goes, you can use the PROPER function to resolve your problem, lickety-split.
Example of 1) =(A1&" "&B1&" "&C1)
assuming cell A1 is the first name, B2 the middle, and C1 the last name.
Example of 2) =PROPER(A1)
Post again if these don't work.
|
| 7 | Tosh
Sustainer
ID: 057721710
Thu, Nov 18, 2004, 23:22
|
I'll post again anyways. Thanks alot fellas. Worked like a charm. Two more questions ...
1. Now that I have a single cell that shows "Recycled Fluid", how do I copy that cell onto a different worksheet or spreadsheet? I keep getting a REF! error. I can't figure out how to convert the result of this PROPER formula into text.
2. I have a long list of names of which I'm trying to remove duplicates. When I run the advanced filter and click the 'unique records only' button, it will remove exact duplicates as advertised. The problem arises when there is a tiny difference. Is there a way for Excel to recognize that "allhair a. allstars" is the same as "allhair a allstars" (notice the period)
|
| 8 | rockafellerskank
Dude
ID: 27652109
Thu, Nov 18, 2004, 23:30
|
2. I don't know.
1. Copy your column and paste-special "values only" and you will strip out the forumula. You are getting an error because the formula is coming with the data to the new sheet.
|
| 9 | allhair allstars
Sustainer
ID: 50902421
Fri, Nov 19, 2004, 00:10
|
1) When you reference your cells, you're pulling the underlying formula you've created, not the data that you see (for lack of a better explanation). To fix this, copy the data you created (selecting the entire column (by clicking on the column header) works well/quickly), and then right-click somewhere in that data area. Select the Paste Special option, and then select Value. Essentially, this copies and pastes the values of the formulas you created. Should solve your problem.
|
| 10 | allhair allstars
Sustainer
ID: 50902421
Fri, Nov 19, 2004, 00:15
|
Hmmm... or what rfs said. :)
As far as #2, Excel looks for exactly unique (is that redundant?) instances. If you have a bunch of middle initials with periods, I recommend doing another text to columns, and parse using a period. Then you can re-concatenate the records that were affected. A little sorting to put all these affected records together should make it easier for you to manage... Clear as mud?
|
| 11 | Challenger
Donor
ID: 481126818
Fri, Nov 19, 2004, 00:36
|
Tosh, re #7 1)
I solve this problem by opening 2 Excel windows and putting one spreadsheet into each and then you can do a simple copy & paste and the formulas will not copy over. This way the format also copies over. However, the column/row width/height settings do not copy over and you will have to readjust them
|
| 12 | Tosh
Sustainer
ID: 057721710
Fri, Nov 19, 2004, 00:51
|
You guys are smarter and friendlier than the computer geek at work. I can hardly wait to get to work and try it out. NOT! Thanks!!
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|