Replacing last four digits in a set of numbers that are separated by dashes

Question

I have a bunch of numbers similar to SSNs in format, and would like to change the final number set to the same. So if the number structure is like this "123-45-6700", I want to have a formula to change the final number ONLY. So the "123-45" portion can be whatever, but I want to be able to change all of the 6789, 6790, 6745, to 6700. Does this make sense? I've been doing replacements, but it's tedious. Thank you!

Mostly find and replace, or manually.

Answer 1

Just like I have posted in comments above using TEXTBEFORE()

---

• Formula used in cell H3

=TEXTBEFORE(G3#,"-",2)&"-6700"

---

Or,

---

• Formula used in cell H3

=REPLACE(G3#,8,4,6700)

---

Or,

---

• Formula used in cell H3

=LEFT(G3#,7)&"-6700"

---

Or,

---

• Formula used in cell G3

=SUBSTITUTE(G3#,RIGHT(G3#,4),6700)

---

Please change G3# to your original range as per your suit. In example I have used this to generate some random series.

 =RANDARRAY(10,,100,200,1)&"-"&
  RANDARRAY(10,,10,50,1)&"-"&
  RANDARRAY(10,,6700,7000,1)

---

As in comments below Solar Mike Sir, pointed out that if it categorically needs to be replaced those which starts with 67 then you could try the following :

---

• Formula used in cell H3

=CHOOSE(N(--LEFT(RIGHT(G3#,4),2)=67)+1,G3#,LEFT(G3#,7)&6700)

---

Answer 2

=TEXTBEFORE(A1,"-",-1)&"-"&FLOOR(TEXTAFTER(A1,"-",-1),100)

Will convert the last for digits to the closest multiple of 100 smaller or equal to given number.

Or =REPLACE(A1,LEN(A1)-1,2,"00")