Deleting stuff
Posted by Ken on June 29, 2001 12:05 PM
I need help deleting some things from a column. Most of the columns begin with 5 letters followed by a hypen and then by four numbers. I need to delete the 5 letters and the hyphen.
Ex. BAPTS-0034.
I want to keep the 0034 and delete all else. Macro or Code. Also some are already just numbers without the 5 letters before hand and hyphen, i would like to keep those and not have them deleted.
Posted by IML on June 29, 2001 12:40 PM
Try this formula (assuming your data is column A) and copy it down. You can then copy paste special over it and delete the first column
=RIGHT(A1,4)
good luck
Posted by Malc on June 29, 2001 1:05 PM
I almost replied as above but some of the cells already have the bit to be extracted.
try
=if(iserror(find("-",A2)),"'"&A2,right(A2,4)) check the syntax or use paste function wizard to get find right. "'" bit keeps A2 as text although I'm sure the text function could be used.
I wouldn't bother with a macro unless it's part of something else already written anyway the code would do something like the formula above between do loop until
Posted by Chris on June 29, 2001 1:10 PM
If this is a one time event, this is what I would do. Select the column and then use Data > "Text to Columns...". Then choose "Delimited," "Next," enter in a hyphen under other. The result is two columns. Delete the old column. If there is still mixed data of letters and numbers, use sort to extract the data you need.
Posted by mseyf on June 29, 2001 1:15 PM
you could try:
=IF(MID(A1,6,1)="-",RIGHT(A1,4),A1)
-Mark
Posted by IML on June 29, 2001 1:21 PM
I think under scenaria, if you had two numbers
adsf-1234
4567
you would end with ADSF and 4567 in the first column (to be deleted)and 1234 only in your "keeper" column.
Unless a read this wrong, I think only the right four digits are what he is after?
