Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function/Macro that keeps first X characters in a cell and delets rest
So , i need a macro/function that keeps the first X characters in a cell ( the number is given ) and deletes the remaining characters Example In a cell i have the following text : *Mother and father* I want the macro to keep the first 8 characters . So , in that cell , the result should be : *Mother a* . When i mean charcaters , i take into consideration letter , space or number -- andrei ------------------------------------------------------------------------ andrei's Profile: 1056 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167722 Microsoft Office Help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function/Macro that keeps first X characters in a cell and delets rest
Let's assume your cell is A1; this line of code should accomplish what you
want... Range("A1").Value = Left(Range("A1").Value, 8) Since this changes the value in the cell itself, you cannot use a UDF (User Defined Function), so I'm guessing you will want to use it in a macro. Your description (as to how you wanted your code to perform) was not detailed enough to decide how to structure the macro for you. -- Rick (MVP - Excel) "andrei" wrote in message ... So , i need a macro/function that keeps the first X characters in a cell ( the number is given ) and deletes the remaining characters Example In a cell i have the following text : *Mother and father* I want the macro to keep the first 8 characters . So , in that cell , the result should be : *Mother a* . When i mean charcaters , i take into consideration letter , space or number -- andrei ------------------------------------------------------------------------ andrei's Profile: 1056 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167722 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function/Macro that keeps first X characters in a cell and delets
Try this out:
Sub leftXChrs() 'keeps left x characters of a selected range Dim c As Range Dim mychrs As Long On Error Resume Next mychrs = InputBox("Be sure to select a range of cells. " _ & Chr(13) & "How many characters to keep?", 1) If mychrs = False Or Not Application.IsNumber(mychrs) Then Exit Sub End If For Each c In Selection c.Value = Left(c.Value, mychrs) Next c End Sub HTH, -- Data Hog "andrei" wrote: So , i need a macro/function that keeps the first X characters in a cell ( the number is given ) and deletes the remaining characters Example In a cell i have the following text : *Mother and father* I want the macro to keep the first 8 characters . So , in that cell , the result should be : *Mother a* . When i mean charcaters , i take into consideration letter , space or number -- andrei ------------------------------------------------------------------------ andrei's Profile: 1056 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167722 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function/Macro that keeps first X characters in a cell and delets rest
thanks , it works -- andrei ------------------------------------------------------------------------ andrei's Profile: 1056 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167722 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get rest of characters after last certain character? | Excel Programming | |||
Macro/function that keeps in a cell a given number of characters | Excel Programming | |||
Macro analyses Cell . If text found , delets entire row | Excel Programming | |||
macro which delets cell if the cell above has the same keyword | Excel Programming | |||
finding the first blank cell and then executing rest of macro | Excel Programming |