Delete in macro
Posted by John Rocha on March 02, 2000 6:09 PM
I have a sheet with a column of telephone numbers all with the same area code ie:708-258-1000. What I would like to do is create a macro to delete the first four characters of each cell and then continue on to the next cell (LOOP) but when I do this all the cells become the same number (258-1000). In the code it comes up as activecell =formula ("258-1000") What can be done as to make the macro only delete the first 4 characters and not change the rest of the entry. I appreciate you help.
Posted by JAF on March 03, 2000 4:34 AM
It's almost certain that you COULD do this in a macro, but Excel has a built in formula that will do it for you.
Assumign you have a value of 123-456-123456 in cell A1, simply type the following in B1
=MID(A1,5,99)
This will return up to characters from position 5, thereby removing the first 4. If your data is of a consistent type, you can reduce the final number accordingly.
Hope this helps.
JAF
Posted by Chris on March 03, 2000 5:10 AM
John,
You can use JAF's formula in a macro if you wish to avoid the additional column. Something like this should work:
Sub asdf()
for each cell in selection
cell.value = mid(cell.value, 5, 99)
next cell
End sub
The above will work for a range you select. You could change it to a Do Until Loop if you wish.
HTH,
Chris
