Partial Cell Deletion Question

[Legacy 178446]

Hello!

I have about 3000 row of cells which all include the word 'Residence ED = XXXXXX'

for example in Row 1 it says 'Residence ED = 5001'

What I need to do is delete the 'Residence ED = 'part from all 3000 rows without deleting the unique number in each cell. Any quick way I can do this with a macro or something?

Its my first time posting I hope Im doing so in the correct manner

Thanks!

 

[JamesW]

Hi,

Welcome to the board.

Ctrl + H -> Find What: "Residence ID = " (without quotes - make sure you include the space after the =),, Replace with: Leave blank.

 

[Legacy 178446]

That was quite painless! thanks for the speedy reply James

 

[JamesW]

Happy to help.

 

[Legacy 178446]

another question,

the digits in each cell are 4 digit numbers eg 1234, is there a way I can put a zero before each number quickly to make 01234

 

[JamesW]

Hi,

You could try a custom format:

0####

 

[Legacy 178446]

Thanks again!

 

[Peter_SSs]

Note that formatting will make it appear that there is a 0 at the front but your actual number does not. If your number is in A1 and you try =LEN(A1) you will find the result is 4.

This may be exactly what you want but if you really want a 0 at the front you could use ..
=0&A1
or
=TEXT(A1,"00000")
.. but on the flip side these would no longer be numbers but text.

Also, if it is the text values you really want, you may have been able to achieve that with the original Find/Replace by typing '0 into the 'Replace with:' box instead of leaving it blank.

 

[Legacy 178446]

Thanks for that Peter il try it that way