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

Answer 1 / 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
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
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello
=IF(A1<>"A","",CNUM(SUBSTITUTE(A1,"A","5")))
0
Floretteuh
 
Perfect, thank you :)
[ Fortunately there are people like you to help people like me ]
0
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