Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   How to truncate '.' only from the beginning of a string in MS Excel (https://www.askmehelpdesk.com/showthread.php?t=453764)

  • Mar 3, 2010, 02:48 AM
    anish.vatsya
    1 Attachment(s)
    How to truncate '.' only from the beginning of a string in MS Excel
    I am generating an output in MS Excel . Only in one column I want to truncate '.' from beginning. If it is not starting with then leave it.

    Example :
    Input
    Col 1
    .06R215.23
    .02145.HS
    12HJ458
    12.232.32

    Output required
    Col 1
    06R215.23
    02145.HS
    12HJ458
    12.232.32
  • Mar 3, 2010, 04:08 AM
    KISS
    =if(left(c1,1)=".", mid(c1,2,len(c1)-1),c1)
  • Mar 3, 2010, 05:59 AM
    anish.vatsya

    How to apply this?? Please help
  • Mar 3, 2010, 06:22 AM
    KISS
    1 Attachment(s)


    It's not a macro and it won't change how an item is displayed, but will create a new cell of the correct form. The cell must be text. If you had an entry of .01, then enter it as '.01 which will force it to be TEXT.
  • Mar 3, 2010, 11:21 AM
    JBeaucaire

    This macro will do the same thing on the original data. It operates on the values in column B only.
    Code:

    Sub TruncateItemIDs()
    'JBeaucaire  3/3/2010
    Dim Cell As Range

        For Each Cell In Range("B:B").SpecialCells(xlCellTypeConstants)
            If Left(Cell, 1) = "." Then Cell = Mid(Cell, 2, 99)
        Next Cell

    End Sub

    =======
    How/Where to install the macro:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

  • All times are GMT -7. The time now is 04:44 AM.