brothertruffle880
asked on
Excel VBA - Delete the first character in a cell and move down to next cell
I have a "island" of cells. a9:a230
I would like to have a macro:
1. Press F2 (edit the cell).
2. delete the first character in the cell.
3. Press ENTER
4. Move down one cell
Repeat steps 1, 2, 3, 4
I would like to have a macro:
1. Press F2 (edit the cell).
2. delete the first character in the cell.
3. Press ENTER
4. Move down one cell
Repeat steps 1, 2, 3, 4
ASKER
Using the macro recorder didn't delete the first character of the cell
The macro recorder re-input the existing cell contents for that one cell.
I need to edit the cell --regardless of the cell contents.
then I need to delete the first character, regardless of it's character.
The macro recorder re-input the existing cell contents for that one cell.
I need to edit the cell --regardless of the cell contents.
then I need to delete the first character, regardless of it's character.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also do it using the Data...Text to Columns menu item:
Sub StripFirstCharacter()
Range("A9:A230").TextToColumns Destination:=Range("A9"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1)), TrailingMinusNumbers:=True
End Sub
ASKER
Thank you Byundt. Just what the doctor ordered.
From the developer tab choose record macro and give it a name and a shortcut key.
Follow the sequence you have described (f2,home,del,enter) until step 4 - then stop the macro. you can now use the shortcut key to do them one after another
alternatively put the following formula into a different column (eg b9)
"=mid(a9,2,len(a9))" and copy that down the column.
If you want to just retain the output - not the formula - then select the new column and copy, past special, values