how would i change these cells?

[bigdan]

I have a spreadsheet of store numbers that I need to change around. The first 3 characters are always ABC, and then it follows with 2-4 digits. I need to make these all 4 digits, with the missing characters being made 0. So for example ABC1234 would stay as is, ABC123 would become ABC0123, and ABC12 would become ABC0012.

How would I do this?

 

[lrobbo314]

Here is a UDF that seems to do what you are looking for.

Function FormatAlpha(s As String)
Dim L As String: L = left(s, 3)
Dim R As String: R = Replace(s, L, "")
FormatAlpha = L & Format(R, "0000")
End Function

 

[Fluff]

With a formula
=LEFT(A2,3)&TEXT(MID(A2,4,100),"0000")

 

[Rick Rothstein]

Here is another formula solution that you can consider...

=REPLACE(A2,4,0,REPT(0,7-LEN(A2)))