convert text to predefined number in single column

[chlee72]

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

 

[Peo Sjoblom]

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"

 

[Mark Graesser]

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
(e-mail address removed)

----- 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