Hi To do them in situ, you would need a macro, or use Find/Replace 4 times. To do them in an adjacent column, you could use something like: =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) Hope this helps. -- Andy. "slh" <slh@discussions.microsoft.com> wrote in message news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com... >I have a field that can contain one of four different text values, BV, BR, > FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and > FR=1. > Can I do this with one formula? > -- > slh

0 |

5/16/2005 12:08:32 PM

Pleased to help and thanks for the feedback! -- Andy. "slh" <slh@discussions.microsoft.com> wrote in message news:61CBCB89-BEC3-4705-AC9E-A3BB5EE2B622@microsoft.com... > Thanks Andy, > The formula to list them in an adjacent column worked perfectly. > -- > slh > > > "Andy B" wrote: > >> Hi >> >> To do them in situ, you would need a macro, or use Find/Replace 4 times. >> To do them in an adjacent column, you could use something like: >> =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) >> Hope this helps. >> >> -- >> Andy. >> >> >> "slh" <slh@discussions.microsoft.com> wrote in message >> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com... >> >I have a field that can contain one of four different text values, BV, >> >BR, >> > FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and >> > FR=1. >> > Can I do this with one formula? >> > -- >> > slh >> >> >>

0 |

5/16/2005 2:00:01 PM

Hi The way the formula works is by treating each part of the formula as a sum. The first bit looks whether A2="BV" and returns a TRUE or a FALSE - which is then coerced into being a 1 or a 0 by multiplying (*) it by whichever value you want the result to be. If BR was in A2, for example, the formula: =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) would create a sum of: =(0*5)+(1*4)+(0*6)+(0*1) which gives a result of 4. With a text result, however, you'll need to use IF statements - unles there are a lot of options. =IF(OR(A2="P001",A2="P002"),"SPER",IF(OR(A2="P004",A2="P005"),"MPER",IF(A2="P102","MH","None of these"))) Hope this helps. -- Andy. "slh" <slh@discussions.microsoft.com> wrote in message news:E15A8418-981A-4382-9CFB-65C8D832B3A7@microsoft.com... > Andy, > A couple of questions if I may, > 1. I have another similar situation that I tried to modify your formula > to > accomplish with no luck. This time it is text converted to text. Ex > "P001" > or "P002" = "SPER", "P004" or "P005" = "MPER" and "P102" = "MH". > > Also can you tell me what the *means in the formula you gave me. > Thanks, > -- > slh > > > "Andy B" wrote: > >> Pleased to help and thanks for the feedback! >> >> -- >> Andy. >> >> >> "slh" <slh@discussions.microsoft.com> wrote in message >> news:61CBCB89-BEC3-4705-AC9E-A3BB5EE2B622@microsoft.com... >> > Thanks Andy, >> > The formula to list them in an adjacent column worked perfectly. >> > -- >> > slh >> > >> > >> > "Andy B" wrote: >> > >> >> Hi >> >> >> >> To do them in situ, you would need a macro, or use Find/Replace 4 >> >> times. >> >> To do them in an adjacent column, you could use something like: >> >> =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) >> >> Hope this helps. >> >> >> >> -- >> >> Andy. >> >> >> >> >> >> "slh" <slh@discussions.microsoft.com> wrote in message >> >> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com... >> >> >I have a field that can contain one of four different text values, >> >> >BV, >> >> >BR, >> >> > FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 >> >> > and >> >> > FR=1. >> >> > Can I do this with one formula? >> >> > -- >> >> > slh >> >> >> >> >> >> >> >> >>

0 |

5/17/2005 8:03:40 AM

No problem! -- Andy. "slh" <slh@discussions.microsoft.com> wrote in message news:3B6BCC11-A38D-40D6-BD72-584452583B0B@microsoft.com... > Thankyou for taking the time to explain, that helps me a lot! I am a bit > of > a novice with formulas so I really appreciate it. > -- > slh > > > "Andy B" wrote: > >> Hi >> >> The way the formula works is by treating each part of the formula as a >> sum. >> The first bit looks whether A2="BV" and returns a TRUE or a FALSE - which >> is >> then coerced into being a 1 or a 0 by multiplying (*) it by whichever >> value >> you want the result to be. If BR was in A2, for example, the formula: >> =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) >> would create a sum of: >> =(0*5)+(1*4)+(0*6)+(0*1) which gives a result of 4. >> With a text result, however, you'll need to use IF statements - unles >> there >> are a lot of options. >> =IF(OR(A2="P001",A2="P002"),"SPER",IF(OR(A2="P004",A2="P005"),"MPER",IF(A2="P102","MH","None >> of these"))) >> Hope this helps. >> >> -- >> Andy. >> >> >> "slh" <slh@discussions.microsoft.com> wrote in message >> news:E15A8418-981A-4382-9CFB-65C8D832B3A7@microsoft.com... >> > Andy, >> > A couple of questions if I may, >> > 1. I have another similar situation that I tried to modify your >> > formula >> > to >> > accomplish with no luck. This time it is text converted to text. Ex >> > "P001" >> > or "P002" = "SPER", "P004" or "P005" = "MPER" and "P102" = "MH". >> > >> > Also can you tell me what the *means in the formula you gave me. >> > Thanks, >> > -- >> > slh >> > >> > >> > "Andy B" wrote: >> > >> >> Pleased to help and thanks for the feedback! >> >> >> >> -- >> >> Andy. >> >> >> >> >> >> "slh" <slh@discussions.microsoft.com> wrote in message >> >> news:61CBCB89-BEC3-4705-AC9E-A3BB5EE2B622@microsoft.com... >> >> > Thanks Andy, >> >> > The formula to list them in an adjacent column worked perfectly. >> >> > -- >> >> > slh >> >> > >> >> > >> >> > "Andy B" wrote: >> >> > >> >> >> Hi >> >> >> >> >> >> To do them in situ, you would need a macro, or use Find/Replace 4 >> >> >> times. >> >> >> To do them in an adjacent column, you could use something like: >> >> >> =((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="FR")*1) >> >> >> Hope this helps. >> >> >> >> >> >> -- >> >> >> Andy. >> >> >> >> >> >> >> >> >> "slh" <slh@discussions.microsoft.com> wrote in message >> >> >> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com... >> >> >> >I have a field that can contain one of four different text values, >> >> >> >BV, >> >> >> >BR, >> >> >> > FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, >> >> >> > FM=6 >> >> >> > and >> >> >> > FR=1. >> >> >> > Can I do this with one formula? >> >> >> > -- >> >> >> > slh >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>

0 |

5/18/2005 9:47:59 AM

I have a table with time in this format "12:00:00 AM". I 'd like to extract the time in these two formats 1) 12:00 (12-hour) 2) 12:00 (24-hour) Thanks a lot, dear experts ! -- Allen Phailat Wongakanit "ali" <ali@discussions.microsoft.com> wrote in message news:991B1441-9E1B-42C8-A3E5-AF8061D4EDD7@microsoft.com... >I have a table with time in this format "12:00:00 AM". > > > I 'd like to extract the time in these two formats > > 1) 12:00 (12-hour) > 2) 12:00 (24-hour) Tables don't store formatted values. Th...

I have 2000 columns with accounting type text numbers in each cell, i.e. 0345999, 058956, ect. I want to add an "0" in front of each account number. Anyone know how to do this withount going into each cell and doing it by hand? Hi one way: use a helper column with the formula ="0" & A1 copy this down for all rows, copy this column and insert it with 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany Stacy wrote: > I have 2000 columns with accounting type text numbers in > each cell, i.e. 0345999, 058956, ect. > > I wan...

Many companies, including Microsoft, have 5 digit extensions for their phone numbers. i.e xxx-xxx-xxxx ext. xxxxx Dynamics is limited to 4. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/...

D'all, In a column, I hav 16 digits numbers. But when I add 4 digit number after last digit of that number becomes 4 zeros at last. e.g. 9503170011002910 (16 digit number) 1425 (4 digit number) becomes 95031700110029100000 whether it should be 95031700110029101425 Excel can only handle 15 digit numbers, after that everything gets shown as 0. If you want 20 digits you will have to hold it as text. -- __________________________________ HTH Bob "Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message news:8E87C8C7-8CBC-4CF8-9E0A-F222DD023255@mi...

I am writing a history about my Dad and Mom. I want to create an index showing the pages specific names are mentioned. How do I do this. Publisher does not have an Index feature. Use Word. -- Mary Sauer http://msauer.mvps.org/ "family history mama" <family history mama@discussions.microsoft.com> wrote in message news:BFF1FE4A-E80F-444F-8E7D-6AA71878D100@microsoft.com... >I am writing a history about my Dad and Mom. I want to create an index > showing the pages specific names are mentioned. How do I do this. ...

Hi, I have a cell where if the cell is empty (A1=0), then I would like the cell to display "Enter your value here." Otherwise, I would like the cell to display the value a person enters. I have tried using an if/then/else statement and, understandably, I get a circular reference error: =IF(A1=0,"Enter your value here",A1) Does anyone have a suggestion? Thanks! Why not just enter the text itself? The value entered by the user will displace (replace) it. This should work if the form is a template. -- HTH, RD ===================================================...

How to I convert the spreadsheet to be all in caps? I'd use a macro like Chip Pearson's: http://cpearson.com/excel/case.htm If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm excel issue wrote: > > How to I convert the spreadsheet to be all in caps? -- Dave Peterson ...

Hi Guys, How can I determine the most common occurence of a word in a lis formatted as text in Column A. I would like a function that simpl returns the word that occurs the most to appear in cell D1. Also, i there are two words that occur the same number of times in this list, would like both words to be shown (one in D1, one in D2). Any suggestions? Thanks, Be -- Message posted from http://www.ExcelForum.com Hi to get the most frequent text entry try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX($B$1:$B$20,MODE(MATCH(IF($B$1:$B$20<>"",$B$1:$B$...

Hi all, Question: How can I convert the numerical option group value from a form to a string value and place it in the control source of a textbox in a report header? I have a form used to 'build' reports: users choose a query, a report, and then the rest of the controls on the form are used to build a 'where' clause for the DoCmd.OpenReports statement attached to a command button which opens the report based on all the user's choices. I've tried creating a variable on the report in the declarations area and then using a select case statement in the 'open&...

After converting lotus files to excel... the outline of each cell as dispalyed on the screen disappears to reveal a white only background. How do I get the outline of each cell to reappear? Thanks John You could make your own list picking the data from here http://cherbe.free.fr/traduc_fonctions_xl97.html make it into a table and use vlookup I also believe Norman Harker is working on one.. Look in the newsgroups for his name and email him and if your home is good he might send you one for free <g> -- Regards, Peo Sjoblom "John" <jfrasercpa@cs.com> wrote in message...

Can a cell be edited if the contents are the result of a vlookup function and still keep the rseults? I have 2003 on an XP Pro PC. My formula is =IF(A4<>"",VLOOKUP(A4,InvDetail!$A$2:$B$3000,2,FALSE),"") I trying to create an availability list for our customers but need to add comments to the end of some items. I know I can add another column but the customers are used to the comment being after the item description and space on the page is limited. Thanks, Lee Coleman If you're talking about entering comments manually, then you'd need to concatenate th...

My internet connection is through Cox Cable and is continual access; I am a Yahoo Plus subscriber and receive virtually all of my email thru yahoo, and my permenant address is a yahoo address. I own MSOffice Enterprise 2007 which, of course includes Outlook 2007; I want to set Outlook up as my communications program for home, office, and mobile (I have 6.1 on my mobile.). Program Outlook 2007 to be my email server; keep my current yahoo email address as primary; connect outlook 2007 through my esisting cable connection; and import my existing contacts files into outlook for usage a...

I have long wanted to convert my quicken files to Money. I now find that Money can not convert any Quicken file from 2007 onward. Does anyone know if this will ever be supported? If not, what is the work around? I want to use Money as the small business invoicing function seems to be a bit more flexible. Please help Joanne M. Jacksonville, FL ...

Hi. I need to do numbering of duplicate values and assign these numbers to a table. Example: Table: Field1 1 2 3 2 3 2 4 1 Reult I need: Field1::Field2 1::1 2::1 3::1 2::2 3::2 2::3 4::1 1::2 where "::" is a field separator. Additional assumptions: - Field1 is a text field - Updating is necessary (new data will appear in Field1 and should also be numbered) Thanks for help Kamil Something like SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND P1.FIELD1=P.FIELD1) AS ROWNUM, P.* FROM P HtH Pieter <kamil.jedrzejewski@gmail.com> wrote in message news:1193131909....

X-No-Archive: yes I need help to sort the contents of a cell. Cell A1= 01201 I wish to sort the numbers in A1 into descending numerical order. The result should be =21100 What is the simplest formula that I can use to achieve this? I would like to learn how to do this. Thamks On 10 Jun 2005 03:56:44 -0700, virfir97@yahoo.com wrote: >X-No-Archive: yes > >I need help to sort the contents of a cell. >Cell A1= 01201 > >I wish to sort the numbers in A1 into descending numerical order. The >result should be =21100 > >What is the simplest formula that I can use to ac...

I have a series of cells that will hold values such as 350,000.00. This value is entered by the user and entire amount is used in calculations across the worksheet. I want the output to be 350k. This way, more columns can be viewed at a time and makes for easier reading than a number with all the zeros. I can't get the customer number format to "hide" the zeros. WB Thanks, yes it did. "Carmen" <hovendic@yahoo.com> wrote in message news:Od0aSCQrGHA.4356@TK2MSFTNGP02.phx.gbl... > WB, I put > > #,k > > in the custom formatting box and it looks ...

I have two separate worksheets Worksheet A COLUMNA COLUMNB Insurance Company Name Maketer NAME Aetna John Smith BCBC Mary Doolittle Abbington Mike Doe Worksheet B COLUMNA COLUMNH Insurance Company Name Marketer Name Aetna BCBC Abbington I need the Marketer Name to show up in Colu...

I have a windows server 2008 r2 and am running remote desktop services. I want to disable cut/paste of files via clipboard/clipbook, while at the same time enabling cut/paste of text via clipboard/clipbook. This is working in earlier version of Windows. Any suggestion is much appreciated. -- Hannkwang ------------------------------------------------------------------------ Hannkwang's Profile: http://forums.techarena.in/members/199146.htm View this thread: http://forums.techarena.in/windows-server-help/1319418.htm http://forums.techarena.in ...

I was wondering if it was possible to have flashing text in a cell that contains a formula. And if so is it complicated to do? see: http://www.cpearson.com/excel/BlinkingText.aspx -- Gary''s Student - gsnu200748 "Scott" wrote: > I was wondering if it was possible to have flashing text in a cell that > contains a formula. And if so is it complicated to do? Thankyou Gary's Student that's just what I needed. "Gary''s Student" wrote: > see: > > http://www.cpearson.com/excel/BlinkingText.aspx > > > -- > Gary'...

Trying to loop to load different textbox Textbox1, Textbox2, Textbox3 ect n = 1 xCt = 15 xText = 35 Do while xCt > 0 Textbox(n) = xText n = n + 1 xCt = xCt - 1 xText = xText + 3 Loop It would help you get a helpful answer if you provided... Excel version? Where the code is located? Where the textboxes are located What kind of textboxes are they (or how you created them)? What is the problem? What is the question? -- Jim Cone Portland, Oregon USA http://www.contextures.com/excel-sort-addin.html (30 different ways to sort in excel) "Gordon" <gwelch...

Is it possible to change the "result of a formula"... to a number? Hi yes, choose the cell(s) you want to do this to and copy them then choose edit / paste special - values this will change the formals to the result. Cheers JulieD "Renee R." <Renee R.@discussions.microsoft.com> wrote in message news:E99C248C-B6AC-416D-BA24-1302BC503B50@microsoft.com... > Is it possible to change the "result of a formula"... to a number? ...

Is it possible permanently save the Restore Point to specific date (one day) as file? No it's not. Restore points are not complete backups, they depend on the integrity all newer restore points to work - thus they cannot be used alone. "anime" <anime@nospam.microsoft.news> wrote in message news:uq00jx09KHA.2248@TK2MSFTNGP05.phx.gbl... > Is it possible permanently save the Restore Point to specific date (one > day) as file? "Dave and Rosanna" <davexnett05@yahoo.com> wrote in message news:ey8Oeh29KHA.4652@TK2MSFTNGP06.phx....

Front page will not have a Next Edition. AFAIK it is dead. > Ed Bennett wrote: > > Ron Hagley wrote: > >> I have a document with Headings on one line followed by a linespace > >> then on a new line a subheading followed by (on the same line ) a > >> description > >> > >> I wish to use TEXT STYLES as this pattern repeats many times, I have > >> no problem seting up the styles for the headings, but when I do the > >> same for the SUBHEADINGS the text style is applied to the WHOLE line > >> including the description...

I want to use either Publisher or Word to create curved text. The first part of my text is to curve downwards, as if it was sitting on a invisible circle. The second part of the text is to curve upwards, as if it was also sitting on a invisible circle. I looked in the index at the back of the large 1,583- page "Inside Out" manual and cannot locate the instructions. Did the programmers forget to include this function when they designed the program? In Publisher 2000 you would use Word Art (the Big red W blue A on the left side of the screen). After creating your text cha...

how do I delete a highlighted selection using the backspace button and then continue using the backspace without clicking the mouse? I found a lot of threads that address the first part of the question (checking the "typing replaces selection" box in options/edit tab. However, my issue is that I want to continue deleting text by hitting the backspace button. The cursor seems to be limited to the position of the selected text. I have to move the cursor position after deleting the selection (with the backspace button).... I recall that this is possible because i do this ...