View original (French)

Replace a letter with numbers in Excel

Solved
Floretteuh -  
tchernosplif Posted messages 714 Status Member -
Hello,

I’m reaching out because I have a problem, which I think isn’t very complicated, but it’s wasting me a lot of time.
So I’m on Excel 2003, I’d like that when I type A in one cell it gives me the value 5, then in another cell A should become 1 ...
Is there a special formula to do this?

Thanks in advance
Flore

Configuration: Windows XP / Internet Explorer 6.0

1 answer

  1.
    tchernosplif Posted messages 714 Status Member 249
     
    Hello Florette,

    2 possibilities:

    name a cell A; if this cell contains 5, then in another cell writing =A equals writing 5.

    Otherwise there is the SUBSTITUTE function to replace characters; this requires writing the formula.

    =SUBSTITUTE(A1,"A","5")
    if in A1: TATA => T5T5
    3
    1. Floretteuh
       
      Thank you for the answer, the formula =SUBSTITUTE(A1;"A";"5") is exactly what I was looking for, but it raises another problem: the A is transformed into 5 in the line that I need, but when I sum its column the 5 is not taken into account in the sum...
      0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Hello
      =IF(A1<>"A","",CNUM(SUBSTITUTE(A1,"A","5")))
      0
    3. Floretteuh
       
      Perfect, thank you :)
      [ Fortunately there are people like you to help people like me ]
      0
    4. tchernosplif Posted messages 714 Status Member 249
       
      yes because the number is considered text, you have to multiply it by 1 to convert it to a number.
      0