# Delete first two numbers...

#### m0atz

##### Board Regular
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Weaver

##### Well-known Member
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

##### Active Member
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

##### Board Regular
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

##### Well-known Member
="0" & MID(A1,3,50)

Should work

#### m0atz

##### Board Regular
legendary thank you..!

#### jasonb75

##### Well-known Member
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

##### Board Regular
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.

Replies
3
Views
201
Replies
13
Views
809
Replies
3
Views
323
Replies
0
Views
256
Replies
1
Views
451

1,191,226
Messages
5,985,373
Members
439,961
Latest member
drose1105

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back