Automatically edit text in a column

Once a month I take a list of .jpg and import them into a Excel. I then edit the names one at a time. Is there a way to mass edit these?
Here is an example:
DanPurcell.jpg and I want it edited to Dan Purcell
The names always have the last name capitalized.
I would even settle with just mass getting rid of the .jpg extension.
I have attached a file.

Thanks
Dan PurcellManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you tried pressing Ctrl+H and Find .jpg and Replace with (nothing)?

How do you import the file names into Excel? Do you use something like Filecats Standard or something similar?
0
Dan PurcellManagerAuthor Commented:
Yes, that did work to get rid of the .jpg
I have a macro to import the file names.
Sub FileNameCopy()
Dim strNew As Worksheet
Dim strName As String
Dim intFiles As Integer

Const strPath As String = "P:\Agent photos Need uploaded to site\Jan2015-Now\IR\color"

intFiles = 0
strName = Dir(strPath & "\*.jpg")
Do While strName <> ""
    If intFiles = 0 Then Set strNew = ActiveWorkbook.Worksheets.Add
    intFiles = intFiles + 1
    strNew.Cells(intFiles, 1).Value = strName
    strName = Dir
Loop
MsgBox "Copied " & intFiles & " filenames."
End Sub
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then change

strNew.Cells(intFiles, 1).Value = strName

To

strNew.Cells(intFiles, 1).Value = left(strName, len(strName)-4)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

MacroShadowCommented:
Or you can use this function to accomplish all you requested:
Function Demo(strInput As String) As String
    Dim i As Long
    ' Remove extension
    strInput = Left(strInput, Len(strInput) - 4)
    ' Loop through all characters in strName one at a time and rebuild string
    For i = 1 To Len(strInput)
        ' If current character is in Upper Case
        If Mid(strInput, i, 1) = UCase(Mid(strInput, i, 1)) Then
            ' add a space before the Upper Case character
            Demo = Demo & " " & Mid(strInput, i, 1)
        ' If current character isn't in Upper Case
        Else
            ' Just add the character
            Demo = Demo & Mid(strInput, i, 1)
        End If
    ' Move to next character
    Next
    ' Remove leading space
    Demo = Trim(Demo)
End Function

Open in new window

Then change:
strNew.Cells(intFiles, 1).Value = strName

Open in new window

To:
strNew.Cells(intFiles, 1).Value = Demo(strName)

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Here's a slightly different approach that will split names that may include a middle name. It assumes the names are in Column A and starts at A1

Option Explicit

Sub CleanText()
'
    Dim Rcl As Range '

    '
    Sheet1.Range("A1").CurrentRegion.Columns(1).Replace What:=".jpg", Replacement:="", LookAt:=xlPart, _
                                             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                                             ReplaceFormat:=False
    Dim NewText As String
    For Each Rcl In Sheet1.Range("A1").CurrentRegion.Columns(1).Cells
        NewText = SplitAtCap(Rcl.Value)
        Rcl.Value = NewText

    Next Rcl
End Sub
Function SplitAtCap(str As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "([a-z])([A-Z])"
        SplitAtCap = .Replace(str, "$1 $2")
    End With
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan PurcellManagerAuthor Commented:
I will award points next week once I've got time to play with these. Thanks to you all for your input!
0
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 168 points for Phillip Burton's comment #a40703866
Assisted answer: 166 points for MacroShadow's comment #a40703902
Assisted answer: 166 points for Roy_Cox's comment #a40704534

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Dan PurcellManagerAuthor Commented:
Sorry, I got really busy at work. Have no objection to closing and points awarded as above
0
Dan PurcellManagerAuthor Commented:
D
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.