Ask Experts Questions for FREE Help !
Ask
    anish.vatsya's Avatar
    anish.vatsya Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Mar 3, 2010, 02:48 AM
    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
    Attached Files
  1. File Type: xls Example.xls (13.4 KB, 165 views)
    2. KISS's Avatar
    KISS Posts: 12,510, Reputation: 839
    Uber Member
    		  
    #2

    Mar 3, 2010, 04:08 AM
    =if(left(c1,1)=".", mid(c1,2,len(c1)-1),c1)
    anish.vatsya's Avatar
    anish.vatsya Posts: 2, Reputation: 1
    New Member
    		  
    #3

    Mar 3, 2010, 05:59 AM

    How to apply this?? Please help
    KISS's Avatar
    KISS Posts: 12,510, Reputation: 839
    Uber Member
    		  
    #4

    Mar 3, 2010, 06:22 AM


    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.
    Attached Files
  2. File Type: xls Example-2 mod.xls (13.5 KB, 226 views)
    3. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #5

    Mar 3, 2010, 11:21 AM

    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.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

(Microsoft Excel) - Need to extract a specific number from text string [ 2 Answers ]

For example, cell K1 looks like this Z. Thornton(90), J. Bornstein(90), A. Jazic(65), C. Talley(90), Y. Cuesta(90), S. Kljestan(90), P. Nagamura(90), M. Lahoud(90), J. Braun(90), J. Padilla(46), Maicon Santos(60) I want a formula that will give me the number to the right of a name if the cell...

Beginning of Alien abduction [ 5 Answers ]

When did people first begin reporting alien abductions?

In The beginning [ 8 Answers ]

I am a lesbian. In the beginning, my Partner and I had sex at least 2 times per day! My partner was very affectionate; she would always kiss me, ask for a kiss, hug me, had her hand on my leg while driving, she was always all over me. My partner would often say, "When is this honeymoon going to be...

I'm beginning to think I'm wasting my time [ 2 Answers ]

So for the past year I have been on and off with this guy. For the most part he is a pretty good guy. But we have had issues. In apirl I found out that I was pregnant and had a miscarriage. When I told him he really didn't care about anything except the fact that I was a good thing he wasn't...

Beginning my own busniess [ 3 Answers ]

How do I began my own busniess?

View more questions Search