convert text to predefined number in single column

Hi all,

Greeting to all of you out there!

I am a newbie to this forum and now I have a question here.

I have a worksheet which contains thousands of records. Within a
particular column (column "N") there are single character in the cells
throughout the records (eg. "M", "S").

My question is how can I convert from "M" to "1" and "S" to "2" using
formula?

Kindly advise.

Thanks and best regards.


Eric


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 2:16:43 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
333 Views

Similar Articles

[PageSpeed] 34

Use a help column,

=LOOKUP(N1,{"M","S"},{1,2})

copy down as long as needed
copy and paste special as values in the N column

or if the reason you want this is to total the values you can do it with one
formula


=SUMPRODUCT(COUNTIF(N1:N1000,{"M","S"}),{1,2})

will give you the total values of the "M" and "S"

-- 

Regards,

Peo Sjoblom

"chlee72 >" <<chlee72.10021t@excelforum-nospam.com> wrote in message
news:chlee72.10021t@excelforum-nospam.com...
> Hi all,
>
> Greeting to all of you out there!
>
> I am a newbie to this forum and now I have a question here.
>
> I have a worksheet which contains thousands of records. Within a
> particular column (column "N") there are single character in the cells
> throughout the records (eg. "M", "S").
>
> My question is how can I convert from "M" to "1" and "S" to "2" using
> formula?
>
> Kindly advise.
>
> Thanks and best regards.
>
>
> Eric
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
1/14/2004 2:40:13 AM
chlee72
You can also do this with Replace

1) Select column 
2) Edit>Replace on the pull down men
3) Replace M with 
4) Replace Al
5) Repeat for 

Good Luck
Mark Graesse
mark_graesser@yahoo.co
    
     ----- chlee72 > wrote: ----
    
     Hi all
    
     Greeting to all of you out there
    
     I am a newbie to this forum and now I have a question here
    
     I have a worksheet which contains thousands of records. Within 
     particular column (column "N") there are single character in the cell
     throughout the records (eg. "M", "S")
    
     My question is how can I convert from "M" to "1" and "S" to "2" usin
     formula
    
     Kindly advise
    
     Thanks and best regards
    
    
     Eri
    
    
     --
     Message posted from http://www.ExcelForum.com
    
     
0
anonymous (74722)
1/14/2004 2:16:08 PM
Reply:

Similar Artilces:

A Combination Chart using a Column and Line
I have a large amount of data about protein values and I have grouped these using the Analysis-Pak Histogram wizard. The resulting frequencies have been charted using the 'Column' Chart Type. Using the NORMDIST function I then plotted the appropriate Normal Curve as a 'Line' Chart Type on the same chart but using a secondary y-axis. I was wondering if there is any sensible way of scaling the NORMDIST values so that both sets of data can be charted on the same axis. Any suggestions will be gratefully received. ...

Format a certain column
I would like to format a certain column, meaning for example the 5th column from the left to be seen. This should not automatically be the E-column!! If for example the B and C column are hidden, I want the format to be for the G-column. Does anybody have an idea how to do that?? Thanks, Teresa, By "format," you mean use a certain fill color, or something like that (the Excel meaning of "format")? If so, a macro will have to do it if columns are being hidden and unhidden, deleted and inserted, and the formatting must change dynamically. -- Earl Kiosterud www.smoke...

Text wrap in Publisher 2003
Hi all! Doesn't Publisher 2003 recognize automatically white areas around pictures like Publisher 2000 when using text wrap around the picture? Only way to do this automatically - that I know - is to save picture with Alpha Channel. EB Publisher 2003 works the same as 2000, just a little more manual work. With bitmaps you have to adjust the wrap points, but there are far fewer and the points are exactly where you put them. In 2000 the wrap points were arbitrarily placed, line art images end up with large numbers of wrap points at times. -- Mary Sauer MS MVP http://office.microso...

Text boxes
I have code that creates a graphic and text boxes automatically. But when I try add drag the text box over the graphic its is masked by the graph and does not go "on top of it". I don't understand why because when I manually draw a text box and drag it over the graph it shows? I checked the properties and they are the same. WHat could be worng? Thanks B. --- Message posted from http://www.ExcelForum.com/ Checked the z-order of the respective objects? -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Offic...

Convert Time Into A Number To Multiply By A Currency.
This is what I am trying to do: creating a spreadsheet to forcast a employee work schedule, I am inputting the start time and finish time totalling each position and totalling all the hours, so far so good Now what I want to do is convert the total time into a number, done only problem is the number only works as long as the hours do no exceed 24 so how do I create a formula which will recognize the numbe under 24 hours as is and recognized the number above 24? example: for the total of 109:10 hours I am using the formul =HOUR(B1)+(MINUTE(B1)/60) but I get the total 13.17 I am looking fo the...

Multiple numbers in a cell separated by a comma, need separate cel
Multiple numbers in a cell separated by a comma, need separate cells. Is ther a way to take the contents of cell A1 (contains: 192410, 192413, 90704, ....) and "separate" the data to individual cells (without entering manually)? Thanks. Use text to columns facility. Works even when data is not text. Paste data into A1 then highlight extent of data just in col A. Hit text to columns. Chose delimited. Next. Delimiters comma (uncheck others). This should now have appearance of columns. Next. Format cols as general. Finish. Russell Dawson Excel Student Ple...

Exporting email with atachment to text file
I would lke to have a backup of all my email in a pure text form. Many years ago I used Netscape email, and the entire mail file was pur text; attachments followed the message and were encoded in base64. I could always retrieve my email (with a little effort) by cuting and pasting, and using uuencode to retrieve the binary form of the attachment. Is there any way to do this in Outlook? I have recently encountered difficulties with different versions of Outook at home and work, so would like to avoid problems like this in the future by having a purely text email file. TIA <dtshedd@yah...

line-column combination chart, with stacked columns
I am trying to make a combination chart with stacked colums and an overlayed line (or data point), just like it is possible to make a combination chart with "regular" bars and a line. Hi, Have you tried creating the combination chart yourself? Create a stacked columns chart, including the series you want to plot as a line. Then select this series and use Chart > Chart Type to change the series into a line chart of your choice. Cheers Andy annewcpss wrote: > I am trying to make a combination chart with stacked colums and an overlayed > line (or data point), just like ...

Converting Money File
Hi I have Microsoft Money 2005 (US version). I am planning to purchase Microsoft Money 2005 (UK Version). Is there an easy way of converting the money file as I understand that they both are not compatible to each other. Please suggest. Thanks No easy way. Even the ugly ways don't work so well. Suggestion is to start over and figure out a way to avoid worrying about migrating old data. "Bala" <Bala@discussions.microsoft.com> wrote in message news:C4623D9F-3C23-4265-AB38-644FE5812FEE@microsoft.com... > I have Microsoft Money 2005 (US version). I am planning to...

How can I search for and display actual record numbers in Access?
I have a form that has several fields to save data in for achieve purposes. I also have a search form that pulls that data and displays it, each in an individual line. It would be ideal to be able to click on a search result and then on the main form have that record be displayed. I do not know how to do that, so I was hoping to just be able to display the record number in the search results so that you can just type it in the main form and go right to that record number. I am unable to do that either, I tried several combinations of displaying something like me.currentrecord for ...

how do I right flush part of a single line
In wordPerfect, if I want to hard right flush PART of a single line of text, I simply hit alt-F7. How do I do that in MS Word 2003 Thanks Joe Matthews Gibsons, BC On Sun, 13 Dec 2009 17:06:01 -0800, Joe Matthews <JoeMatthews@discussions.microsoft.com> wrote: >In wordPerfect, if I want to hard right flush PART of a single line of text, >I simply hit alt-F7. > >How do I do that in MS Word 2003 > >Thanks > >Joe Matthews >Gibsons, BC Microsoft Word is very different from WordPerfect in this and many other respects. In Word, you set...

How to set default delimiter for text files...
Hello, Database produces output files as text delimited files with CSV extension. Problem is that different versions of ms excel has different default delimiter: comma or semicolon. How to configure ms-excel: default field separator (delimiter), to be the same on all workstations opening these files ? Open must be full automatic - just by clicking CSV file in the explorer, users are not a gurus.. Thanks and regards, GG On Thu, 8 Jul 2004 13:33:21 +0200, "GG" <iksio1@no_spam.gazeta.pl> wrote: >Hello, > >Database produces output files as text delimited files with C...

Linked Excel table with forced text wrap
I have an Excel table linked to Access. The text in Excel is forced to wrap (I've tried both Alt-Enter and using Char(10) in an expression - same result). Char(10) or Alt/enter code shows up as a little box in Excel with no forced wrap. Any ideas? Here's the text in Excel (shows up as text, not dates) "12/12/05 1/03/06 3/22/06" Here's what I get in Access 12/12/05(little box)1/03/06(little box)(little box)3/22/06 ...

How to enter a new Office 2004 serial number.
Hello All, Does anyone know how to use a new serial number? I had to buy another license for a machine that already had Office 2004 installed. "Bucky" <dh@plushstudios.com> wrote in message news:1103668044.322182.288040@c13g2000cwb.googlegroups.com... > Hello All, > Does anyone know how to use a new serial number? I had to buy another > license for a machine that already had Office 2004 installed. It was some time since I installed Office for Mac but I seem to remember that Office will ask for a serial number as soon as you start any program in the Office suite. ...

How can I translate text in many cells of an Excel sheet from English to Greek with VBA
Hi all, I have a spreadsheet with thousands of rows and columns of text that I'd like to translate into Greek. Can anyone tell me how I might do that using VBA with some free service or API online? Many thanks in advance. Kim Kim wrote, on 01st March 2012 00:11 UTC + 1 : > Hi all, > > I have a spreadsheet with thousands of rows and columns of text that > I'd like to translate into Greek. > > Can anyone tell me how I might do that using VBA with some free > service or API online? > > Many thanks in advance. > > Kim Hello, This is a quite classical...

I'm stumped, splitting up a clump of text into useable information
Here's the problem: The products we sell have a product name, each product has one or more grades of the product (which usually relate to the quality of it) for each grade we have a package size and a unique number called a "SKU" that identifies that grade and package size in our system. I need to take the source code of a page with all of this information and turn it into an excel sheet with columns titled "product" "grade" "packaging" "sku" for each of the grades. The format of the html source code throws in all sorts of charact...

X axis on 0 on Line-Column on 2 Axes
Is there a way on a Line-Column on 2 Axes chart to have the 0 value on both the bar and line axes be straight across from each other even though the bar chart is in 1,000s and the line axes is in single digits and has negative numbers? Only way I can figure is to put negative numbers in the bar axis as well. Try this: http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Supe" <Supe@discussions.microsoft.com> wrote in message news:F1174954-513D-...

Column/Row Formating
I am using Office XP which inclued Excel 2002; my problem is my row/column formating will occasionally change without any input from me. I have several cells merged and do not have width set to adjust for the amount of text,just text wrap. Any ideas, I have to keep correcting my formats. Thanks, Gary C. Gary Merged cells will allow "wrap text" from Format>Cells>Alignment but.......... Excel has a problem auto-fitting merged cells. You can resize manually or via Macro. Jim Rech has written code for this. http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkm...

page numbers #14
I am writing a series of booklets for use in eastern Europe. I am in the process of setting up a template but running into some complications (first time I have used any version of Publisher; version 2007 is version I'm using). It is in metric (105 x 148.5cm), consists of 32 pages and after the first page, facing pages (last page is also single to keep publication with 4 page set up). I want the first and last pages of the template to be blank; the second and third pages in the template just to contain at bottom "TEI © 2008" but starting at page 4 (with facing page 5)...

Copy Folders and it's content from a text file.
I have a script that searches a drive for a specific folder name and writes to a text file with the path. I would like to create a sub routine to then read the text and make a copy of that folder and contents to a USB Hard drive. (If possible with a progress bar to show me the status of the copy operation . ) Here is a copy of the code that I'm using. Thank you all in advance. -Jeremy Code: Set objRegEx = CreateObject("VBScript.RegExp") Set objFSO=CreateObject("Scripting.FileSystemObject") Set objFile=objFSO.CreateTextFile("C:\SearchLog.txt&quo...

Can't select text in input control
I have created a custom page that works fine within CRM. The strange thing is that I can't select (highlight) text in a textbox control. I created the page using an asp:TextBox control and a regular HTML Input control set to type=text. For some reason I can't select the text using the keyboard or mouse in either case. The standard CRM pages don't exhibit this behavior. Does anyone have a clue what's going on? Thanks. James Ooops! I found it. My form template had the following JavaScript event: onselectstart="return false;" Not too smart. ...

Filter data from one collum by looking up from another column
Hi, I am stuck on the following problem: I want to exclude names (or give them another color) from 1 column if they are also in another column. To illustrate this, the result from the following data: A B 1 John Peter 2 Sue Floyd 3 Peter Dan 4 Mike Chris would have to be: John Sue Mike Or in this case I could use a conditional formatting to paint A3 red. How do I do this? Try something like this: Using your posted example: Select A1:A4, with A1 as the active cell From the Excel main menu: <format><conditional formatting> Formula is: =COUNTIF($A$1:$B1,A1)>1 Click the...

how do i place numbers in ticket template so that they increment?
i have downloaded a template for event tickets, that show only one ticket. I want to print more than 1 ticket to a page, and I want to have them numbered sequentially. Does anyone know how I can do this, with a macro perhaps? Use a mail merge. And print preview will fib to you - print a test page. ;-) -- JoAnn Paules MVP Microsoft [Publisher] "david peters" <davidpeters@discussions.microsoft.com> wrote in message news:720CC242-05A7-4552-80DB-6381E7E2A240@microsoft.com... >i have downloaded a template for event tickets, that show only one ticket. >I > want ...

converting an access database to a web page in frontpage
i need help converting this database in frontpage....I have the database linked to a web page in frontpage. however it is only a table and I cannot convert the table to all input in the fields. YOU KNOW HOW THE $6.00 WILL GET YOU THOUSANDS STARTS, I found this on a bulletin board and decided to try it. WELL, I FIGURED THAT IT WAS A LOAD OF CRAP, I THOUGHT THAT WHY ON EARTH AM I GOING TO SEND ANY MONEY TO PEOPLE THAT I DON'T KNOW.FOR WHAT, TO BE ON SOMEONE'S MAILING LIST. COME ON FOR GOODNESS SAKE, I AM A FINANCE MAJOR, I THOUGHT NO WAY. SO AFTER ABO...

2003 documents converted to 2007, lines drawn in are misaligned
I have 100s of documents converted over to 2007 and anywhere there were lines drawn in from the "shapes" menu - they are not lining up properly on the page when printed out. ...