X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Text File Conversion, Access 2016    
post Mar 7 2018, 09:00 PM

Posts: 777
Joined: 31-May 04
From: Wisconsin, USA

Hello all!

I am working with a text file created from a folder directory. I imported the text file into Excel--but I don't know enough about Excel to do what I want to do. I want to remove everything from the left of the first Letter of the string:

11/15/2017  08:45 PM       387,282,808 Baggage Claim (2013).mkv

I've manage to get it to this (not exactly what I want):


Now how do I insert a space between the lower and upper case? There are 1,302 files and they have anywhere form 1 to 5+ words in the title.

Though, in a perfect world my new field would be: Baggage Claim (2013) ohyeah.gif


I've now managed to get it to BaggageClaim (2013), but still stuck on the space between words.

This post has been edited by LilAnnCC1: Mar 7 2018, 09:07 PM
Go to the top of the page
post Mar 7 2018, 09:18 PM

Posts: 1,899
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

Do you have some sample data you can share?
Seems the location of the text you need to capture varies from record to record.
We will need some representative data to see if there is a pattern or algorithm that may work.
Go to the top of the page
post Mar 7 2018, 09:28 PM

UtterAccess VIP
Posts: 6,458
Joined: 30-June 11

What about something like

Function ExtractFileName(ByVal sInput As String) As String
     On Error GoTo Error_Handler

      'Remove the Date (static 20 chrs long)
     ExtractFileName = Trim(Right(sInput, Len(sInput) - 20))
     'Remove the numeric value
     ExtractFileName = Mid(ExtractFileName, InStr(ExtractFileName, " ") + 1)

     On Error Resume Next
     Exit Function

     MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ExtractFileName" & vbCrLf & _
            "Error Description: " & Err.Description & _
            Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
            , vbOKOnly + vbCritical, "An Error has Occured!"
     Resume Error_Handler_Exit
End Function

To Test

?ExtractFileName("11/15/2017  08:45 PM       387,282,808 Baggage Claim (2013).mkv")

Which returns

Baggage Claim (2013).mkv

but we'd need more sample of the data to confirm it's reliable for all cases.

If the date segment isn't static at 20 Chrs long then you could simply test for AM or PM and work from there quite easily.

Go to the top of the page
post Mar 7 2018, 10:33 PM

Posts: 777
Joined: 31-May 04
From: Wisconsin, USA

Oh Joy! Happy Dance! Happy Dance!

Thank you so much, Daniel Pineault! It works perfectly! I've just spent the last hour fixing my list manually and you solved it in minutes! I ran your function against my original list and it was perfect!

Thank you again!
Go to the top of the page
post Mar 8 2018, 11:53 AM

UtterAccess VIP
Posts: 6,458
Joined: 30-June 11

My pleasure. thumbup.gif
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd March 2019 - 07:41 PM