In excel how do i get from this 44345678912 to this 0345678912 us.

[Twoways]

I have in column A list of numbers looking like this 44345678912 and i need
to place a zero in front of the number but also taking away the two fours, i
know the formula for adding the zero ="0" & [A1] but can anyone help with
removing the two fours or a quick way to doing one at a time?

 

[Luke M]

Assuming you're always dropping the first 2 digits:
="0"&RIGHT(A1,LEN(A1)-2)

If you're speciifcally looking for double 4's:
="0"&SUBSTITUTE(A1,"44","")

Do note that both of these result in a text string, not a number.

 

[Don Guillett]

Formula
="0"&RIGHT(C4,LEN(C4)-2)

macro

sub fixnums()
for each c in range("a2:a22")
c.value="0"&"&RIGHT(C,LEN(C)-2)
next c
end sub