Delete first two numbers...

[m0atz]

How do i loop through all the values in col D and remove the first two numbers? for example, a value in D1 is 447712444877 and i want it to read 7712444877. I have 200+ of these hence not wishing to change them manually..?

cheers

colin

 

[Weaver]

in the next available column

=MID(D1,3,50)

If it's phone numbers, 50 should be plenty

If you didn't know the max length then

=MID(D1,3,LEN(D1)-2)

 

[EOAEvan]

If your wanting a formula solution you could try:

Change the last arguement to a higher number if some of your values are more digits than your example.

=MID(D1,3,10)

 

[m0atz]

thank you both - this works great.

there may be a requirement to add the "0" to the start of all these numbers as well, to make them into normal phone number type formats, is this easy to do as well?

Cheers
​

 

[Weaver]

="0" & MID(A1,3,50)

Should work

 

[m0atz]

legendary thank you..!

 

[jasonb75]

Looks like it's solved but thought I'd throw in my alternative anyway.

=REPLACE(D1,1,2,0)

Or a failsafe that leaves the original number unchanged if it doesn't have the 44 prefix.

=IF(LEFT(D1,2)="44",REPLACE(D1,1,2,0),D1)

JB

 

[m0atz]

good options and i'll certainly note them as i'm sure the syntax will be useful for other requirements on my project.

thanks all for the responses.