Hi All,

I have a large table, with some cells having a character "#" in between some of the texts. For eg, there is a special character "#" in the text FESM#RAMAN. I wish to find the # character and then delete ALL the letters to the right of it in that particular cell, i.e. I wish to be left with FESEM only in the cell. I have recorded a macro for this, which goes to the home (Ctrl+Home), finds #, then the macro performs "del" operation repeatedly (i.e. the first instance of "del" deletes the # since it is selected, and then the subsequent instances of "del" delete the following letters R,A,M etc.
My problem is that I do not know before-hand exactly how many characters are there following #, although I can safely assume it to be 20 characters max, so in the macro the "del" operation is repeated about 20 times. However, I observe that (say for the above case FESM#RAMAN, after deleting # and the letters R,A,M,A,N, it deletes the whole content of the cell, leaving a blank cell behind. This problem is not apparent when I record the macro or when I replicate the process manually (as the cursor reaches in the end and the subsequent "del" have no effect), but when this recorded macro is run, the next press of del deletes the contents to the left also (within the cell). This is came to know by stepping into macro and using F8.




Please suggest (preferably using record-macro operation as I am not conversant with VBA) how to delete only the desired letters, leaving, for eg, FESM intact.
[Version: Office 365, Windows 10]