Formula help... #3

In column A there are cells with team names in them like the following:
NYY
NYY
NYY
@OAK
@OAK
@OAK
BOS
BOS
BOS
@TEX
@TEX
@TEX

I would like column B to list all the team names without the @ symbol and 
column H to list all the teams with the @ symbol.  For the example I would 
like column B to have 
NYY
NYY
NYY
BOS
BOS
BOS


0
Jambruins (119)
2/21/2005 6:39:11 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
321 Views

Similar Articles

[PageSpeed] 7

=COUNTIF(A:A,"@*")

and

=COUNTIF(A:A,"<>@*")

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Jambruins" <Jambruins@discussions.microsoft.com> wrote in message
news:135965C4-7523-47AB-88DE-D0B12B652CD5@microsoft.com...
> In column A there are cells with team names in them like the following:
> NYY
> NYY
> NYY
> @OAK
> @OAK
> @OAK
> BOS
> BOS
> BOS
> @TEX
> @TEX
> @TEX
>
> I would like column B to list all the team names without the @ symbol and
> column H to list all the teams with the @ symbol.  For the example I would
> like column B to have
> NYY
> NYY
> NYY
> BOS
> BOS
> BOS
>
>


0
bob.phillips1 (6510)
2/21/2005 6:53:07 PM
In column B
=IF(LEFT(A1)="@","",A1)
In column H
=IF(LEFT(A1)="@",A1,"")

-- 
Regards
Roger Govier
"Jambruins" <Jambruins@discussions.microsoft.com> wrote in message 
news:135965C4-7523-47AB-88DE-D0B12B652CD5@microsoft.com...
> In column A there are cells with team names in them like the following:
> NYY
> NYY
> NYY
> @OAK
> @OAK
> @OAK
> BOS
> BOS
> BOS
> @TEX
> @TEX
> @TEX
>
> I would like column B to list all the team names without the @ symbol and
> column H to list all the teams with the @ symbol.  For the example I would
> like column B to have
> NYY
> NYY
> NYY
> BOS
> BOS
> BOS
>
> 


0
roger5293 (1125)
2/21/2005 6:58:18 PM
Roger, that is what I wanted but is there a way to have it get rid of the 
blank cells at the same time?  Right now it gives me
OAK
OAK
OAK



NYY
NYY
NYY

Is there a way to have the NYY move to right underneath the OAK?  Thanks.

"Roger Govier" wrote:

> In column B
> =IF(LEFT(A1)="@","",A1)
> In column H
> =IF(LEFT(A1)="@",A1,"")
> 
> -- 
> Regards
> Roger Govier
> "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message 
> news:135965C4-7523-47AB-88DE-D0B12B652CD5@microsoft.com...
> > In column A there are cells with team names in them like the following:
> > NYY
> > NYY
> > NYY
> > @OAK
> > @OAK
> > @OAK
> > BOS
> > BOS
> > BOS
> > @TEX
> > @TEX
> > @TEX
> >
> > I would like column B to list all the team names without the @ symbol and
> > column H to list all the teams with the @ symbol.  For the example I would
> > like column B to have
> > NYY
> > NYY
> > NYY
> > BOS
> > BOS
> > BOS
> >
> > 
> 
> 
> 
0
Jambruins (119)
2/21/2005 7:15:02 PM
Reply:

Similar Artilces:

spreadsheet formula #2
Good morning, i have a manager who wants to average a set of cells. he there are indefinate number of cells in a row and he wants to average only the current entry plus only the last three consecitive entries. example: if a1,b1,c1,d1,f1 all have entries he wants the average based on the info in b1,c1,d1,f1. when e1 has data entered he wants the average to be based on c1,d1,f1,e1. there are 20+ rows that need to have this function. can someone help, this is beyond my capabilities. thanks doug Doug, This will total it for row 1 =SUM(N(OFFSET(A1,,LARGE((1:1<>"")*(COLUMN(I...

Microsoft Certification -help
Hi All...Its been great being a part of this group.I am working on VC++(MFC) for the past 9 months.I thought of giving some certification exam.Side by side i have installed visual studio.net 2003 on my machine and i am getting working on visual c++.net...are there any certification exams which i can take ...please let me know if any one of you knows about it or has taken the same....If yes then please refer me some books which i need to go through..Please help me as it is very confusing on the microsoft website... Waiting eagerly for a reply.. Harsh Nanchahal. ...

please help, is it possible to find...
Dear Sir/Mam Is it possible to detect that "the text in a MS-Word 2003 document has not been typed manually" or copied from other sources like notepad or clipboard, etc., Could it be possible to find, actually what i mean to say is when someone copied some text in notepad it will be stored in clipboard temporarily right? and now if they pasted that text into a MS-Word 2003 document, actually they didn't typed it manually right. At this situation is there any process to find that the text they just pasted into the word doc has not been typed manually rather it is br...

I need advanced help with the TOC, please!
I am correctly selecting & adding the text I want in the TOC but sometimes an entire block of text is added. I tried deselecting the unwanted portion and all that did was remove it all. This is very frustrating, I've been working on this for several hours now and would just bail on the TOC if I didn't need it. Any help would be greatly appreciated. Thanks! I'm going to try and answer this -- and it's not easy to explain. Your TOC is pulling in the text from the document. You probably only want the first line of a heading and not the rest. (I hope you ...

My category heirarchy got messed up
I just noticed today that my category database seems to be messed up. I'm using Money2003 Deluxe. I started to describe this using generic names, but it was too confusing. So, I'll try it with actual names: The problem: I used to have an Expense category called Personal Care with two subcategories, Fitness and Haircuts. Today, I noticed that these two categories seem to have disappeared from my Money file. They do not show up under Personal Care on the "Set up your Categories" screen. Transactions which formerly had these categories assigned are now mostly showin...

Need a little help setting fonts...
Hello, I need to change the fonts on my applications so that they can use the Registered and Trademark symbols. The Trademark symbol doesn't appear to be in the MS Sans Serif font. MS Word with that font does show it though. Anyway, I've noticed that W2K and XP now use a differnt font. I've forgotten the name of it at the moment. Anyway, I've discovered a minor problem with the way Windows and fonts work and could use some help determining the correct resolution. I've tried changing the fonts in all my windows to the new font. This works pretty much okay. Ho...

Can't Open .pdf Attachments....Help
I receive the error message "Unknown File Format" when attempting to open .pdf files attached to e-mails. I can use Adobe outside of Outlook Express. However, when inside Outlook Express, the program does not recognize the .pdf format. Any suggestions? Find a PDF file on your computer. Right click it. Select Open with. Put a check in the option to always use this program to open files of this type. Find the Adoble Acrobat reader, and double click it. "Jeff" <jeffmwa@maui.net> wrote in message news:01c701c34af5$5faad520$a301280a@phx.gbl... > I receive the ...

Help with reading attributes
Hi, I have this xml structure <listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <rs:data ItemCount="1"> <z:row ows_AZNamnUppdragsansvarig="Peter" ows_AZTypAvUtbetalning="Arvode till privatperson" ows_AZPersonnummer="196202081276" ows_AZPlusgiro="5456436534" ows_MetaInfo="1;#"...

Outlook 2003 sync prob. Please help if you can...
Hi, My network runs Exchange 5.5 at the Mailserver. I have laptop clients recently upgraded to Office 2003 who use Outlook 2003 for email synchronization. I have created two Send/Receive Groups called Online and Offline. When online and the user shuts down, the synchronization dialigue box appears, doesn't come up with any errors, but runs too fast. If you open Outlook again and choose the offline option you can see that synchronization didn't take place as a test email sent and sucessfully delivered in onlune mode does not appear in offline mode. However if I do a manual sync using F...

Help!!! Pivot table from mac goes totally blank on pc.
I'm trying to port some spreads from a mac to a pc. There are pivot tables on the spreads that refresh fine on mac Excel 2004. But when refreshed using Excel 2003 on windows, the data in all the pivot tables goes totally blank. Is this a known bug, or should it work? Any suggestions as to how do debug the problem? Thanks! ...

Can not seem to get Csharp to talk to the sql compact 3.5 database
Hi I am new to csharp. I am trying to get my Csharp windows app to connect to the sql compact database. I am using Visual Csharp Express 2008 and I created the database using the Csharp express IDE. It is in the project folder. I created the tables and added elements to the table manually with the IDE. So the IDE seems to connect to the database. I can also contact the database using the SQL Server management studio. When I created the database I had the connection string placed in the config.app file. A string does appear to exist there. When I run the app. I can not seem to g...

NETWORKDAYS
How do I use the "networkdays" formula to determine when a produc should ship by? I have a PO issue date and a shipment due date, I need to calculate "Should ship by date". I know that it takes 30 days on the water, an I can't include weekends. Help -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=27564 On Fri, 5 Nov 2004 09:42:32 -0600, ajpowers <ajpowers.1f991b@exce...

HELP: Search a formula inside of a Cell
I am trying to compare some old spreadsheets and upgrade to the new and have a case in try to determine if a value was added to a formula inside a cell. So I would like to do a search/find on the contents of the FORMULA in the cell. Example: Cell A1 Contains a formula +F12+F34+F67 Is there a function that can search the formula? Example: FIND "F34" in cell A1. Everything I can see so far you can only search the results, not the formula. Thank you in advance for your help!!! Mike formula are strings. Usually I use the FIND in the worksheet menu to locate the form...

MSCRM 3.0 WebForm UI changes
For those of you who had a chance at looking into MSCRM 3.0, did you notice any major UI changes? We have customized our CRM (including the UI) to a large extent and we need to study the impact of changes in the upcoming version of CRM. Any info would help. Thanks Vimal You can take a look at my blog. The latest post has some links to webcasts of demos of v3. The UI is quite extensively changed. It resembles Outlook Web Access - very easy to navigate, but much different than current version. As the previous poster said, if your changes were of the "supported" variety you sho...

Macro's #3
I'm creating a worksheet that contains check boxes. Upon opening the file at a later date, I get an error when trying to change from design view to normal view. The error says that the file was opened without enabling the macros. I did not get a prompt to enable any macros when I opened the file. And there doesn't seem to be any macros listed in the file at this time. What do I need to do to get this corrected so that I can continue to work in design view and work with the worksheet normally? Thank you in advance for any help that can be offered. Maybe your security level...

Error opening e-mail history items after upgrade from 3.0 to 4.0
Hi! I get the following error when opening e-mails on a customers history after upgrade to version 4.0. I have found similar errors in version 3.0, but no fix for 4.0. Anyone know what to do? vent code: 3005 Event message: An unhandled exception has occurred. Event time: 24.04.2008 12:48:24 Event time (UTC): 24.04.2008 10:48:24 Event ID: 5cc053bde0514d0cbadbb45bf5c3dd7b Event sequence: 32607 Event occurrence: 10 Event detail code: 0 Application information: Application domain: /LM/W3SVC/5/ROOT-1-128534487130402833 Trust level: Full Application Virtual Path: / ...

You do not have permission to send to this recipient #3
Hi, from my company we could not send e-mail to one domain we get e-mail from them. My company is not blacklisted, we do not have any problem sending e-mail except this company. This is the error message Your message did not reach some or all of the intended recipients. Subject: XXXXXXXXXXX Sent: 2/20/2005 1:22 AM The following recipient(s) could not be reached: xxxx@xxxxx.com on 2/20/2005 1:22 AM You do not have permission to send to this recipient. For assistance, contact your system administrator. <server name#5.7.1 smtp;550 5.7.1 Access denied> Thanks "...

need formula for multiple columns/rows
I have a spreadsheet with a budget value in column C, monthly billings in columns D - Q, and the remaining balance in column R. I can use =c3-sum(d3:q3) for row 3, but is there a formula to repeat the calculation for multiple rows without having to type that into the R cell on every row. I'm sure it is simple, I'm just learning. Thanks, kzas Hi Kzas, If I got it right, you want the formula to calculate the sum for al the rows from row 3 up to the row is appears. If so, then the followin should do it: =$c$3-sum($d$3:$q3) -(assuming that there is only one budget figure)- =$c$3-su...

Customer Sync error....HELP!!!
I was updating a customer in HQ, their billing info. I closed out the client list, then went back into it 5 minutes later and every one of our 10,000 customers now all have the same billing information and extra phone numbers in each customer account. The purchase history is intact as are all the tracking numbers per customer. On top of that, HQ had already sync'd to my other stores and now every store has 10,000 of the same customer. I need help and need it badly and as fast as possible. Anyone have any ideas on how to fix this? ...

Urgent Help with CheckSignature method of SignedXml
Hey Friends, I have a Signed XML that looks like the below I also have the public certificate for this but i am not able to pass the public key of the X509Certificate2 to the CheckSignature method any idea or help? <?xml version="1.0" encoding="UTF-8"?> <B:Envelope xmlns:B="http://schemas.xmlsoap.org/soap/envelope/" xmlns:A="http://www.routeone.com/namespace.messaging.diag#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SOAP:Header xmlns:SOAP-SEC="http://schemas.xmlsoap.org/soap/security/2000-12" xmlns:S...

Help creating a cost range
I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You Try a VLOOKUP. See: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "tysonstone" <tysonstone@discussions.microsoft.com> wrote in message news:4F45699F-5C22-4E4F-98E5-BE246543CCD4@microsoft.com... >I need help creating a cost r...

Need Help (i'm at the end of my rope) WMP wont work Missing DRM
Hello, i konw i'm new here but i'm really at the end of my rope. i had to upgrade my motherboard and processer not long ago, and when i did i seemed to lost my drm folder in windows. i have tried everything i can find online, including upgrading windows vista to windows 7 (no good) show all hidden files to delete all files in drm folder. (no drm folder) went to that upgrade drm thing microsoft has and i cant because the button is greyd out or i get a error in internet explorer. running a reg cleaner for missing dll's. i'm losing it. i need major help. please ...

why is the $ sign used in formulae
See help on absolute and relative referencing. Gord Dibben MS Excel MVP On Thu, 28 Aug 2008 22:11:52 +1000, "kandk" <keithandkath@optusnet.com.au> wrote: ...

Gretaer Than Zero Formula Question
I have a formula that puts a currency value in a cell (assume A1). That value can be anything from �0.00 upwards. Dependent on other factors, if the calculated value of Cell A1 is �0.00 (zero pounds), the formula may enter �0.00, or it may leave the cell blank (i.e. using value "" in the formula). An adjacent cell (assume B1) has to perform a calculation on Cell A1 only if the value of Cell A1 is greater than �0.00 (i.e. �0.01 and upwards). I was trying the formula: =IF(A1>0,ROUND((D6/(100-N6)*N6),2),"") The formula works fine when Cell A1 is �0.00 (Cell B1 sho...

help me
how can I open multi files in VC++! thanks ...