UtterAccess.com
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    
 
   
LilAnnCC1
post Mar 7 2018, 09:00 PM
Post#1



Posts: 754
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:

CODE
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):

BaggageClaim

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

Edit:

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
 
orange999
post Mar 7 2018, 09:18 PM
Post#2



Posts: 1,856
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
 
DanielPineault
post Mar 7 2018, 09:28 PM
Post#3


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



What about something like

CODE
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)

Error_Handler_Exit:
     On Error Resume Next
     Exit Function

Error_Handler:
     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

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


Which returns

CODE
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
 
LilAnnCC1
post Mar 7 2018, 10:33 PM
Post#4



Posts: 754
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
 
DanielPineault
post Mar 8 2018, 11:53 AM
Post#5


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



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


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 09:45 AM