#285876 - 23/08/2006 18:21
Excel help?
|
carpal tunnel
Registered: 08/03/2000
Posts: 12341
Loc: Sterling, VA
|
I'm wondering if this is possible and if so, how:
Say I have 6 columns of information, all of it text (no numbers or equations). I have another column that I want to use to pull info from some of the other columns. Is it possible to automatically pull the info from the other columns so they appear in that 7th column in a desired format?
For example, I have text in the first three columns that look like this:
test1 | test2 | test3
Can I create some sort of function in column 7 that will format its self as:
| test2, test1 (test3) |
Is that possible?
_________________________
Matt
|
|
Top
|
|
|
|
|
#285877 - 23/08/2006 18:46
Re: Excel help?
[Re: Dignan]
|
old hand
Registered: 16/02/2002
Posts: 867
Loc: Oxford, UK
|
Unless I've misunderstood you, this is very straightforward. You can use the ampersand operator to append the different cells into one long string.
Assuming we're using row 1 then cell G1 will contain the following formula:
=b1 & ", " & a1 & " (" & c1 & ")"
Edit: Corrected row and column references.
Edited by AndrewT (23/08/2006 18:52)
|
|
Top
|
|
|
|
|
#285878 - 23/08/2006 18:49
Re: Excel help?
[Re: Dignan]
|
carpal tunnel
Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
|
Sure:
=CONCATENATE(B1;",";A1;"(";C1;")")
_________________________
Bitt Faulk
|
|
Top
|
|
|
|
|
#285879 - 23/08/2006 18:50
Re: Excel help?
[Re: AndrewT]
|
carpal tunnel
Registered: 08/03/2000
Posts: 12341
Loc: Sterling, VA
|
Wow. That's indeed very simple. I wasn't aware you could do that. Thanks for the help!
One more question: how do I then tell Excell that I want the cell to contain the resulting text instead of the equation?
_________________________
Matt
|
|
Top
|
|
|
|
|
#285880 - 23/08/2006 18:55
Re: Excel help?
[Re: wfaulk]
|
carpal tunnel
Registered: 08/03/2000
Posts: 12341
Loc: Sterling, VA
|
Thanks to both of you for the help. The main problem I had was I had no idea how to search for what I wanted to do, either in the help file or on Google. I must say that I was proud of my workaround. I copied the columns I wanted into a new worksheet in the order I wanted, then inserted columns containing the punctuation. I then copied the columns into Notepad, did find and replace to get rid of the tabs and to insert the spaces, and then I was left with the result I wanted. It worked perfectly, but is clearly not as elegant as the solutions you offer  Still, I was happy when it worked
_________________________
Matt
|
|
Top
|
|
|
|
|
#285881 - 23/08/2006 18:56
Re: Excel help?
[Re: Dignan]
|
carpal tunnel
Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
|
Quote:
how do I then tell Excell that I want the cell to contain the resulting text instead of the equation?
That's what the leading equals-sign does.
Edit: Oh, I see what you mean. Nevermind.
Edited by wfaulk (23/08/2006 19:20)
_________________________
Bitt Faulk
|
|
Top
|
|
|
|
|
#285882 - 23/08/2006 19:03
Re: Excel help?
[Re: Dignan]
|
old hand
Registered: 16/02/2002
Posts: 867
Loc: Oxford, UK
|
Quote:
One more question: how do I then tell Excell that I want the cell to contain the resulting text instead of the equation?
Without using VBA to programatically do this then I can't think of a way that this can be achieved automatically.
A right-click, Copy then Paste Special -> values will manually get you the literal text.
Not that it would achieve much but you could use the Val(ue of) operator e.g. for H7 =val(G7) That would hide the more complicated formula but you're still left with a formula!
Edited by AndrewT (23/08/2006 19:05)
|
|
Top
|
|
|
|
|
#285883 - 23/08/2006 19:10
Re: Excel help?
[Re: AndrewT]
|
carpal tunnel
Registered: 08/03/2000
Posts: 12341
Loc: Sterling, VA
|
Thanks again. Paste Special will be fine, and eliminates a ton of the copy/paste of my alternative solution. I'll just create a new column for the equation, then paste the resulting values into the column I want.
_________________________
Matt
|
|
Top
|
|
|
|
|
#285884 - 23/08/2006 19:47
Re: Excel help?
[Re: Dignan]
|
carpal tunnel
Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
|
I have to wonder what you need that step for. You can use the results of that equation in any other cell and if you copy-and-paste into another application, you get the value, not the equation.
_________________________
Bitt Faulk
|
|
Top
|
|
|
|
|
#285885 - 24/08/2006 10:37
Re: Excel help?
[Re: wfaulk]
|
carpal tunnel
Registered: 08/03/2000
Posts: 12341
Loc: Sterling, VA
|
Quote:
I have to wonder what you need that step for. You can use the results of that equation in any other cell and if you copy-and-paste into another application, you get the value, not the equation.
It's like this: Outlook is messed up. MS has put so many quirks into that program it's ridiculous.
I have an attorney in my office who can't figure out why it is that when he's creating a new email, when he types a name into the To: field, for some of his contacts it only suggests them if he starts typing their first names, and for some their last names. Well, after much trial and error and testing on my part, I found that first it takes the nickname data from the "Display as:" field in the contact's properties. The thing I didn't know is that first it matches whatever name you put in there starting with the first letters in the text box, and it will match the email address.
So, if you have the name in there as "Last, First (FirstLast@gmail.com)", then if you've sent that person an email before, it will match either "Las..." or "Fir..." in the To: field of a new email. However, if the Display as: field contains "First Last (FirstLast@gmail.com)", then it will match "Fir..." but it will not match "Las..."
The problem is that the values he has in the Display as: field are all over the place in terms of formatting. So what I wanted to do was to first see how he would like to format it, just so it's consistent, and then make sure they were all correct. That's where I came up with the idea to export his contacts to Excel and find some way to populate all the fields with the correct values. There are other ways to do this within Outlook. For example, if you display the contacts in "Phone List" mode, then include the "Display as:" field as one of the columns, you can go through each contact and delete whatever is in there, then Outlook will repopulate that field with whatever format has been chosen in Tools > Options > Contact Options > Default "Full Name" Order. But when you have as many contacts as these attorneys do, that is not a valid option. I couldn't see any other, so I decided on the Excel method.
_________________________
Matt
|
|
Top
|
|
|
|
|
#285886 - 24/08/2006 10:50
Re: Excel help?
[Re: Dignan]
|
carpal tunnel
Registered: 10/06/1999
Posts: 5916
Loc: Wivenhoe, Essex, UK
|
You could also do it using some VBA to directly change the values in Outlook.
_________________________
Remind me to change my signature to something more interesting someday
|
|
Top
|
|
|
|
|
#285887 - 24/08/2006 11:18
Re: Excel help?
[Re: andy]
|
carpal tunnel
Registered: 08/03/2000
Posts: 12341
Loc: Sterling, VA
|
In my searching, I saw a lot of talk about VBA. The only problem is that I have no idea what it is or how to use it.
_________________________
Matt
|
|
Top
|
|
|
|
|
#285888 - 24/08/2006 11:49
Re: Excel help?
[Re: Dignan]
|
carpal tunnel
Registered: 18/01/2000
Posts: 5683
Loc: London, UK
|
Quote:
In my searching, I saw a lot of talk about VBA. The only problem is that I have no idea what it is or how to use it.
Tools -> Macro -> Macros
Give your macro a name (e.g. RenameContacts), click Create, and then paste the following code into the Sub that's generated:
Code:
Dim mapiNamespace As Outlook.NameSpace
Set mapiNamespace = Application.GetNamespace("MAPI")
Dim contactsFolder As Outlook.MAPIFolder
Set contactsFolder = GetDefaultFolder(olFolderContacts)
Dim contact As ContactItem
For Each contact In contactsFolder.Items
contact.FullName = contact.LastName & ", " & contact.FirstName
Next
Note that I've not tested it -- we're on Outlook 2003 on Exchange, so I don't have any local Contacts folders to try it with.
_________________________
-- roger
|
|
Top
|
|
|
|
|
#285889 - 24/08/2006 12:35
Re: Excel help?
[Re: Roger]
|
carpal tunnel
Registered: 10/06/1999
Posts: 5916
Loc: Wivenhoe, Essex, UK
|
A bug fix and a couple of refinements for Roger's macro: Code:
Dim mapiNamespace As Outlook.NameSpace
Set mapiNamespace = Application.GetNamespace("MAPI")
Dim contactsFolder As Outlook.MAPIFolder
Set contactsFolder = mapiNamespace.GetDefaultFolder(olFolderContacts)
Dim contact As ContactItem
For Each contact In contactsFolder.Items
If contact.LastName <> "" And contact.FirstName <> "" Then
contact.FileAs = contact.LastName & ", " & contact.FirstName & " (" & contact.Email1Address & ")"
End If
Next
Edited by andy (24/08/2006 12:38)
_________________________
Remind me to change my signature to something more interesting someday
|
|
Top
|
|
|
|
|
|
Previous Topic
Index