Modifying text string in Excel with a button...

Posted on 2007-07-25
Last Modified: 2013-12-26
I have Column A that  has the names of files from a folder.  I want to delete the extenstion name and copy the result in Column B using a Control Button.

So A1: file1.wav
     A2: file2.wav

Press button, reult is

    B1: file1
    B2: file2

Question by:Joel_Sisko
    LVL 48

    Assisted Solution

    Is the button required ?

    You can do inserting this formula on cell B1, B2, ...

    LVL 13

    Accepted Solution

    Paste this in the click event of the button:

        Dim R As Range
        Dim IndexLastDot As Integer
        Dim s As String
        Dim I As Long
        Dim emptyrows As Long
        For Each R In Range("A:A")
            If Not IsEmpty(R) Then
                s = Format(R)
                If Len(s) Then
                    For I = Len(s) To 1 Step -1
                        If Mid(s, I, 1) = "." Then
                            IndexLastDot = I - 1
                            Exit For
                        End If
                End If
                Cells(R.Row, 2) = Mid$(R, 1, IndexLastDot)
                emptyrows = emptyrows + 1
                'will stop if finds 10 empty cells in column A
                If emptyrows > 10 Then Exit For
            End If
    LVL 13

    Expert Comment

    (be careful 'cos if your file extension is more than 3 letters (for example .html or .jpeg) then you may have problems using a simple formula)
    LVL 48

    Expert Comment

    Good point rettiseert
    LVL 12

    Author Comment

    Thanks!! The simple formula was good to know as well.


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now