I’m wondering how to use Excel to replace the value in a specific location.
For example, I want to change the value 1.7.1.1 to 1.7.1.- or 1.7.1.10 to 1.7.1.-.
Here’s the example data.
1.7.1.1
1.7.1.2
1.7.1.3
1.7.1.4
1.7.1.5
1.7.1.6
1.7.1.7
1.7.1.8
1.7.1.9
1.7.1.10
1.7.1.11
1.7.1.12
1.7.1.13
1.7.1.14
1.7.1.15
1.7.1.16
1.7.1.17
# Expected result
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
1.7.1.-
I just tried =REPLACE(A1,LEN(A1),1,"-"), but it returned the wrong value when there were more than two digits. (e.g., 1.7.1.17 -> 1.7.1.1-).
I guess that it is due to the fixed length of the replacement, but I just want to replace the number at the last position.
>Solution :
Use TEXTBEFORE() function.
=TEXTBEFORE(A1,".",-1)&".-"
Edit: For older versions may try-
=LEFT(A1,SEARCH("|",SUBSTITUTE(A1,".","|",3)))&"-"
