• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Modifying text string in Excel with a button...

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

Joel
0
Joel_Sisko
Asked:
Joel_Sisko
  • 2
  • 2
2 Solutions
 
jpaulinoCommented:
Is the button required ?

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

=LEFT(A1;LEN(A1)-4)
0
 
rettiseertCommented:
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
                Next
            End If
           
            Cells(R.Row, 2) = Mid$(R, 1, IndexLastDot)
           
        Else
       
            emptyrows = emptyrows + 1
           
            'will stop if finds 10 empty cells in column A
            If emptyrows > 10 Then Exit For
           
        End If
    Next
0
 
rettiseertCommented:
(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)
0
 
jpaulinoCommented:
Good point rettiseert
0
 
Joel_SiskoAuthor Commented:
Thanks!! The simple formula was good to know as well.

Joel
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now