Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to edit and delete one character in a cell
Can anyone please help.
I am trying to write a very simple macro that will just edit a cell and delete the last chracter. Example: I have a column with the following numbers: 1234 5678 9032 I want a macro that I can just convert each cell to the following: 123 567 903 Therefore, stripping the last digit. I don't even mind if it splits the cell into two so the end result is: 123 4 567 8 903 2 Thank, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to edit and delete one character in a cell
You sure you want to do this by macro?
The formula =LEFT(A1,LEN(A1)-1) or similar will do this (may have to introduce some error trapping IF statement to cater for LEN(A1) being fewer than 2 characters long). If you only have to do it as a one-off exercise, you could create a column of the above and copy down, then copy the column and pastespecial Values into where you want it to go, then delete the formula column. "David" wrote in message ... Can anyone please help. I am trying to write a very simple macro that will just edit a cell and delete the last chracter. Example: I have a column with the following numbers: 1234 5678 9032 I want a macro that I can just convert each cell to the following: 123 567 903 Therefore, stripping the last digit. I don't even mind if it splits the cell into two so the end result is: 123 4 567 8 903 2 Thank, David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to edit and delete one character in a cell
Hi David
Select the cells and run this: Sub DelLast() Dim Cel As Range For Each Cel In Selection If Cel.HasFormula = False Then If Len(Cel.Value) 1 Then _ Cel.Value = _ Left(Cel.Value, Len(Cel.Value) - 1) End If Next End Sub HTH. Best wishes Harald "David" skrev i melding ... Can anyone please help. I am trying to write a very simple macro that will just edit a cell and delete the last chracter. Example: I have a column with the following numbers: 1234 5678 9032 I want a macro that I can just convert each cell to the following: 123 567 903 Therefore, stripping the last digit. I don't even mind if it splits the cell into two so the end result is: 123 4 567 8 903 2 Thank, David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to edit and delete one character in a cell
or maybe =INT(A1/10)
-- HTH ------- Bob Phillips "Jack Sheet" wrote in message ... You sure you want to do this by macro? The formula =LEFT(A1,LEN(A1)-1) or similar will do this (may have to introduce some error trapping IF statement to cater for LEN(A1) being fewer than 2 characters long). If you only have to do it as a one-off exercise, you could create a column of the above and copy down, then copy the column and pastespecial Values into where you want it to go, then delete the formula column. "David" wrote in message ... Can anyone please help. I am trying to write a very simple macro that will just edit a cell and delete the last chracter. Example: I have a column with the following numbers: 1234 5678 9032 I want a macro that I can just convert each cell to the following: 123 567 903 Therefore, stripping the last digit. I don't even mind if it splits the cell into two so the end result is: 123 4 567 8 903 2 Thank, David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to edit and delete one character in a cell
Assuming the number is in Cell A1, in Sheet1: Sub delLastFellow() Dim myString As String myString = Worksheets("Sheet1").Range("A1").Value myString = Left(myString, Len(myString) - 1) Range("A1").Value = myString End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to edit and delete one character in a cell
Brilliant. Works great. Thanks.
"Harald Staff" wrote in message ... Hi David Select the cells and run this: Sub DelLast() Dim Cel As Range For Each Cel In Selection If Cel.HasFormula = False Then If Len(Cel.Value) 1 Then _ Cel.Value = _ Left(Cel.Value, Len(Cel.Value) - 1) End If Next End Sub HTH. Best wishes Harald "David" skrev i melding ... Can anyone please help. I am trying to write a very simple macro that will just edit a cell and delete the last chracter. Example: I have a column with the following numbers: 1234 5678 9032 I want a macro that I can just convert each cell to the following: 123 567 903 Therefore, stripping the last digit. I don't even mind if it splits the cell into two so the end result is: 123 4 567 8 903 2 Thank, David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
how to select the first character in a cell and delete the rest | Excel Worksheet Functions | |||
macro to copy and edit then delete a worksheet | Excel Discussion (Misc queries) | |||
Delete Asc(10) character from cell | Excel Programming | |||
How to run my macro on Insert-Rows , Edit-Delete | Excel Programming |