Excel VBA Introduction Part 53.4 - Replacing Strings

welcome to this file excel vba tutorial this video is going to explain how you can replace strings using VBA code now it's all focused around using the replace function in VBA so I'll start with a quick simple look at how you can replace both single and multiple characters in a string before we move on and look at how you can then control the number of replacements that get made and the starting position within the string from which replacements are made we'll have a quick section that talks about how to deal with case sensitivity and then right at the end of the video we'll look at a slightly more practical examples for generating valid file names from the contents of cells so essentially replacing the illegal characters in a string so plenty to do here let's get started okay well the starting point for this video is our fairly standard workbook for the movie data if you need a copy in grab BOM from the link in the video description or alternatively if you watch the previous two or three videos in this series we're going to use exactly the same data for this one so feel free to carry on using that same file whatever you decide to do you'll need to head to the VBA editor so of course you can hit ulsan dec 11 2013 for something simple like replacing strings so this is just to give you the absolute basics of how replacing strings works what about to do is declare a simple string variable so I'm gonna say dim s as string and I'd like to assign a value to that variable so I'm gonna say s equals range a for m dot value finally just for the simple introductory part we're going to debug print the values out string so they'll be able to see what's stored inside it which will obviously be there the phrase King Kong with that film's name so what we'll do now is start looking at how we can start replacing bits of text within that string let's start with something quite basic then all I'd like to do to begin with is replace a letter K in the phrase King Kong with another letter so to do that I'm going to use my variable s and I'm going to assign a new value to it using the replace function you press ctrl + space on the keyboard and look for the word replace just if there's a method sitting there in the intellisense list if I open some brackets or parentheses you'll see if there's a great big long list of parameters there's only three compulsory ones though expression find and replace and all three of these things must be strings so the expression I'm looking with in the string that I'm looking within to replace some tax is what's currently contained in my variable s follow that with a comma the string I'm trying to find is the letter K similar to that as a literal string in double quotes and I've made sure that it's a capital letter K though as well that's important at this point the string I'm trying to replace the letter K with is well we could choose whatever we want let's go with the letter s to begin with if we then have another debug print statement immediately below and print the value of s again if I were to simply run this every team will see that two different versions of the phrase type returns I've got King Kong and sing song and so a bit of a silly example at Madelia this several other silly examples we could have done as well we could have replaced K with P and got ping-pong go K with D and not dingdong and so and so on so you see that whatever less you've looked for whatever string you've looked for it replaces every instance of that string with the new string you've chosen mayor by no means limited to replacing one single character with just another single character you can make these sub strings pretty much as long as you like so just for another couple of slightly silly examples let's have a go with replacing the letter K with a phrase get Li D and if I were to run that subroutine I'll end up with a phrase daily ding-dong that example will make a lot more sense to you by the way if you follow English football if you don't then that will be a little bit of a strange obscure example but anyway there is a reason behind that particular phrasing what else can we do let's have a look at maybe changing the cell reference we pick so let's pick sell a 11:50 rather than a four and now I'm going to replace the phrase or the letter M with the two letters T are another way to run that subroutine I'll end up with the film Magic Mike being converted to tragic strike which kind of changes the flavor of the movie a little bit what else do we do let's have a look at range a 637 and this time I'm going to replace a couple of characters with the single character so sort of flip this around so let's say I want to replace the letters G R with the letter just actually I want to replace this with absolutely nothing I want to replace this with an empty string so effective this is going to take away two letters G R from the name of the film so if I were to execute that subroutine now I'll end up with the grapes of wrath' being converted to the Apes of Rus which should definitely be the subtitle for the next plan and the eggs movie absolutely for sure it's a bit of a shame in this case that the the letter A is in the capital letter so I don't get a sort of proper case where you get this first letter of each word being a capital letter so it might make a little more sense of this if this example makes any sense whatsoever it might make more sense to replace that as gr a with a capital letter a and then if I were to execute that one now it looks a little more like the specific title I was looking for i just clicked into the wrong part of the immediate window there let me just run that one again so you can see that it's working as intended so there we go great to rust of the X of rough occasionally you may need to make a sequence of individual replacements to reach the final string you're trying to achieve so let's make another couple of quick changes to our 17 let's look for cell a 289 this time and what I'm going to do here is replace the phrase Y light with the phrase arable and in order to execute that subroutine at that point I'll end up with the twilight saga being converted to the terrible saga which is a much more appropriate title but we're not quite there with this title yet there's a few other problems I mean there are many problems of the Twilight Saga let's not get into that right now but there are several other problems with this particular title I need to change first of all the word saga I don't know if you guys know of a game called candy crush saga like a mobile phone game candy crush saga it's almost as bad a game as Twilight as a movie but anyway the important thing here is that once upon a time the coming of the made for candy crush saga tried to trademark the word saga which had an implication for another video game that's coming out at the time called the panacea to try to soon it was all a horrible mess anyway it all got resolved the important thing is we might want to avoid using the word saga in our movie title so what I've done is I've looked up the actual definition of the word saga and here it is just quick google saga definition and I've got something here long involved story account or series of incidents so I'm just going to copy that string there and then if I go back to my vb code and have a look at adding another replace so I can say below the original replace I can say s equals replace open some parentheses I can use the same string which at that point will contain this phrase - terrible saga but in here I can look for the word saga individually and then I'll stop that properly I'll get the string that I've just copied and paste it in I think I've just missed off the letter L right at the start so stick that in there as well close the parentheses at the end and then let's have a quick test of that one if I run that one I'll end up with a terrible long involved story account or series of incidents Breaking Dawn nearly there one last replacement to make I'm going to add another replace at the end which will be s equals replace and then again I'm going to replace what's currently held in the S variable I'm going to look for the letter D capital letter D and then replace that with a capital letter Y if I close the parentheses run this one final time I will end up the film title called the terrible long involved story account or series of incidents breaking yawn part two that's a much better name and a much more appropriate title for that absolutely awful film okay let's move on a little bit and look at a couple of the other parameters of the replace function I'm just going to clear the contents of the image at window so we've got a bit of a clear space to work with and then I'm going to make a few modifications to this procedure again I'm going to get rid of or I'm going to change the cell reference from 289 to 279 and then I'm just going to remove a bunch of these replace lines two of them at least to leave us with one single one and what I'm going to do here is I'm going to replace letter F with the letter L so I'm going to say F capital F and then capital L I'm just going to comment out these two lines to start with and then I'm going to run this a regime without the replaced version if I run that one we'll end up with the film Fast and Furious 6 never made much certainly why people be both fast and furious I can imagine being furious if you were last in a race so what also new is great the last limit viewers what makes a huge amount more sense to the title of that film so if we just uncomment those two lines I just commented out I'll end up with this time the last under urea switch I'm not quite sure as an English word so let's what we need to do here is make sure that we only replace the first letter F that we encounter in that title so there's a parameter that relies to control how many instances of a character or a substring that you replace if I click back into the parentheses in the replace method and start typing in commas you'll see that I get a bunch of extra optional parameters and the one that I'm interested in here is the count parameter so I type in a couple of Commerce to reach that one I could have also named my parameter so I could have said expression colon equals s.find colon equals F etc British slightly more convenient just to avoid typing in parameter names so what I want to do in this case is only want to replace the first instance I only want to replace one instance of the letter F so if I were to enter that and then run the subroutine I'll end up with the last unlit furious which makes an awful lot more sense and maybe I think so we've seen how to replace just the first instance of a character in a string but whatever you wanted to replace a later instance so say I wanted to flip this around so it was fast and furious for instance it's not just efficient of course to change the count argument from want to because all that will do our way to remember subroutine is replace the first two instances of letter half if you want to replace a later instance and not touch the first we need a slightly different approach so let's just changes around a little bit let's go for a slightly different film this time I'm going to go for the film in cell 818 this time and then I'm going to change less as I'm looking for from letter O to the letter I and I'm going for the lowercase letters this time and I'm going to remove the extra arguments I filled in earlier on just go back to the first basic three arguments if I run that subroutine at this point I'll get Harry Potter and the Goblet of Fire changed to Harry pitter and the giblet if fire which sounds like a slightly more interesting movie to me I don't know I never watched it very entire Potter sorry what I want to avoid in this case I don't want to change the actual title of the movie too much I want I want to look for the I want to replace Goblet with giblet because that sounds kind of fun but I don't want to replace Potter with pitter or of with if so to achieve that we've got to take a slightly different approach we can solve part of the problem by telling the replace function only to start replacing characters after a certain position in the string so every string in VBA is indexed where the first character is indexed setting number one and the second is number two and three and so on so if we can tell the replace function to only begin replacing characters after the first letter o so that's occupied version number eight so we can tell it to restart replacing characters from position nine onwards that will solve past the problem so let's just clear the contents of the image at window and then let's enter another argument for the first optional parameter to start parameter we tell to start at position number nine in this drink and then if we were to run that one that will solve tasks for the problem for us you can probably see a big big problem with the rest of it so the original string is of course Harry Potter and Goblet of Fire now what I've got starting at position number nine which is the letter T there and got all of the rest of that string with any letter O is replaced with letter eyes instead so the giblet if fire the problem is of course that I've now lost the original part of the string so we'll solve that problem separately in a moment but the cool thing is here we can tell it to start at a particular position now it's a little bit annoying that we had to manually key in the value 9 for this particular technique to work it will be much more convenient if we could have calculated the position at the first letter O and then added one to that position to tell it where to begin replacing characters from so if you watched one of the earlier videos in this little mini series about strings in VBA you'll be familiar with the in string or the in stress function so we replace the number nine with a reference to the in stress function we can tell it to start looking at character number one and then we can say we want to look in our string variable and we're looking for a lowercase letter O where then close the parentheses that little part of the expression will calculate the position number eight that's the first instance of a letter O in the phrase Harry Potter I want to start looking at character number nine so if I add one to that of course that will end up with exactly the same as else as I had last time let's just clear the content of the immediate window and then execute that again and I'll end up with the exact same results but with the convenience of not having to know what position in the first letter o occupies it might also be worthwhile at this point solving the problem of replacing the word of with word if I only want to replace the first instance of the letter O in the remainder of our strings so we can solve that problem as we did earlier on by specifying account so I set the count to 1 so only replace the first instance of the letter O in the remaining part of the string clear the contents of the immediate window and then run that one one more time and that will give me the second half of the string that I want the last remaining problem is to return the left-hand side of the strings I want to get the hairy paw to join all that patch go to form a full film title now one way to achieve that is using the in string function again so if I know what what position in the first letter O occupies I can get all the characters from the left of that side of string or thrown from the left of that characters position in the string to avoid having to write out the in string function again it might make sense to declare a variable that will hold a reference step number so I can say dim let's call it I as integer for now and then I can say that I equals 1 let's try that again I can say I equals and then I'm just going to copy and paste my in string function I copy that there and then rather than putting my in string function inside district what I can do here is replace that with reference to my variable I that helps because what I can now do is also concatenate the first part of the string so rather than just using the replace function what I'm going to do here is say s equals left of the pass comma and the number of characters that I want to return first you see is the first eight characters so that's going to be equal to the letter I so that's my variable which contains the position of the first letter o then I can concatenate that using an ampersand with the rest of the string that I've just calculated here so it's a little bit awkward and I'm not sure you would like this to do things like this in the real world which it should I show you how far you can go with this if I do finally run that subregime I do need end up with Harry Potter and the giblet of fire okay one thing we haven't dealt with so far is the if you have case sensitivity in VBA strings let's have another quick separate subroutine for this one just to avoid having to replace too much in the original one I'm gonna create a new sub called case sensitivity I can spell that properly now we go nearly I will get that promised case sensitivity and I want to begin by declaring a variable called s as a string and I'm going to set s to be equal to range and then the self I'm going to look for this time is a 73 and I'm going to refer to the value property is that range then I'll say debug dot print s just to see what the original value of that string was and then what I would like to do is replace in that string some the set say s is equal to replace and then open some parentheses and then say inside the string I want to replace the letter R which would be a capital letter R with the capital letter W and then if I finally debug print the the resulting string with the book type print s clear the contents of the immediate window and then remote subroutine a lender with the Lord of the Rings return on the king´s replace with the lord of the wings Return of the King so we've got the capital letters ours replaced with capital letter double use what we haven't done is replaced the lowercase letter as you'll see that those are completely untouched so by default as you'll know if you've watched the first video in this little mini series about strings in VBA by default VGA is case sensitive when it comes to comparing strings there are a couple of different ways of making string comparisons case-insensitive in VBA but with the replace function the technique is quite simple there's an extra optional parameter called compare which allows you to choose what type of compare modes you want to use so the defaults in VBA unless you've changed the option compare statements at the top of module that default is binary compare which is effectively case sensitive database compare you wouldn't use in Excel VBA that's reserved purely for access databases which will match the compare mode you've set for the actual database file the text compare mode however is the one Wigan Museum text compare just compares the character whether it's a placate or lowercase it doesn't matter it essentially makes a comparison case insensitive so if I were to set that to VB text compare just clear the contents of the image at window and then finally run laps subroutine again I'll end up with all the letter ARS regardless of whether they were uppercase or lowercase replaced with capital letter double use now apart from the fact that this title nameks no sense whatsoever is completely unreadable one slightly waving about it is and of course even though you replace the lowercase letter ours they were replaced with capital letter Wu's now if you wanted to make sure that the string was was a little more readable than it currently is making sure that we had perhaps proper case where the first letter of each word is a capital letter but every other word in the show every other letter in the same word was a lowercase letter we can do that reasonably easily in VBA with the string conversion function AA string convert function ostracon x' as it's called so we say s equals scr comes and then opens in parentheses we got to refer to the string we want to convert first which is s and then we choose a conversion mode now ignoring the exciting sounding ones that refer to a Japanese character sets like Eragon and katakana what we're going to do here is refer to proper case so it's used propagation and closed parenthesis clear the contents of the image your window and then run that subroutine again we'll end up with the proper case 2 version of that completely nonsensical title now that was quite a simple easy way to show you how the proper case works this was a quite a convenient string to use because the first letter of each word immediately followed a space character and that's how properties works the first letter after each space is converted into an uppercase letter but your strings will always be formatted in quite as convenient aways that let's show you another example well these are scenes subroutine actually let me just copy and paste this entire section of code and drop it in immediately down below we'll make a couple of changes so we won't look for cell a7 t3 but look for cell 11 44 and then rather than replacing the letter R I'm going to replace a letter A with letter E and I want to make sure that it's a text compare rather than a binary compare so we're there's a capital letter A and a lowercase letter A that I want to convert and then of always to run that subroutine what we'll end up with is ant-man being converted to nth men just that was quite a nice subtitle for the Lord of the Rings Return of the King I suppose more for the two towers and Return of the King but let's not split hairs anyway the important thing is that although we had ant-man with a capital M originally we've now got ant men with a lowercase M even though I use proper case not simply because there's a dash symbol separating the words rather than a space so what we could do to solve this particular problem is replace the dash symbol in between ant and a man with a space symbol so you see that in a couple of different ways it depends on whether we want to get the dash symbol back at the end probably the easiest way to do this I think would be inside the stringed converter function rather than converting the existing string I want to convert the replaced version of that string where the dash is converted to a space so let's have a quick go I'm just going to copy and basically just for convenience copy and paste my replace function here and paste that over the top of the S and then close the set of parentheses there and then I'm going to enter a dash character there and replace that with a space character so what that will do is if I were to run this one showing these results is I end up with a space character with nth men but I've lost the dash so I want to replace a - what I could do is replace spaces in the result of this expression with a dash character so let's a bit go if we wanted to do that did you see how ridiculous this can become let's say replace so at the button I'll get there eventually replace open some parentheses this is the string that I'm trying to replace and I want to replace inside that string any space character with a dash character and then every words have run the final result of this I'll end up with the the end men movie now I think you'll agree that up to this point in the video all the examples we've you so far been fairly silly in nature have that have been a bit entertaining for you as well that wasn't quite the point of that of showing you the replace function not just have a bit of fun in the correct circumstance the replace function can be incredibly useful so just to wrap up the video I'd like to create something that's a slightly more practical example that uses the replace function to perform a particularly useful job what we're going to do is generate a bit of code that creates a new text file for every unique film name in this list it's about twelve hundred films here but there are multiple instances of symptoms so there are several instances of King Kong for instance so I would like to do is generate a separate text file for each film title that contains the details for every film with that title now what I would also like to do at that point is use the film's name as the file name so the film title becomes the file name that's going to cause a problem in certain circumstances let's just start by setting up the basics of that procedure I'm going to have that to the VB editor and first of all clear the contents of the immediate window and I'm going to have a brand new module for this as well so going to right click on insert module I'm going to create a new sub resume here called create fill map files this is going to rely on using some techniques with using the Microsoft scripting runtime object library so if I haven't got that one referenced already I need to go to the Tools menu and choose references and then in the list of references that appears I need to scroll all the way down to find Microsoft scripting runtime easy to scroll all the way tasks I just don't micro scripting runtime narratives make sure you place a check in the box next to that line don't just click on to the line you must make sure there's a check there click OK there's a few variables I'd like to create to get started I'm going to say dim FSO as a new scripting dot file system object that helps us to manipulate files and folders I'm going to have a variable that holds a reference to a text file which is what I'm going to open up to write my data into some is a dim TS as scripting dot text stream i'd like to declare available that holds a reference to a range objects and like dim are as range that's going to allow me to loop over the entire list of film titles I'm going to have a variable that holds a name for a folder path I'm gonna generate a foldable all of these files are going to be about well there are 1,200 film than less and not many of the film names duplicating those 1,200 so there's going to be an awful lot of files let's have a variable called folder path as string and that will hold there the name of the folder will generate I'm also going to generate a file named variable so dim a file name as a string and that will hold the filename for each file for each film we look at I'm also going to finally count the number of columns in the datasets and if a team calls as integer just to hold the number of columns I'm going to loop over to write out into the text file next let's generate the folder that I want to store these files in I'm going to store these on my desktop which currently there isn't a folder on the desktop that's got this miss folder on it what I would like to be able to do is check if the folder exists to leave the existing copy and then subsequently recreate it so let's do that by any much of a VP editor again and I'm going to write a quick set of code that's gonna say first of all fold up to half equals the folder path to refer to my users desktop so bearing in mind it might not be me running this code I want to make sure that I can calculate the path to their stop you me and Byron functions so they environ and then ask for the user profile and then I can concatenate to the end of that you can ampersand a literal string that says desktop and then also the folder name or articles films what it could then do is check if that folder exists then delete it subsequently recreate it so I could say if FSO dots folder exists opens in parenthesis pass in the folder path that I've just concatenated then FSO dot delete folder and then I can pass in again the folder path for those for the paths once us pinned and I can generate the folder again from scratch by saying FSO dot create folder I can only do this if the folder doesn't exist which is why I needed to check that it did exist first to delete it so I'm going to episode create folder and then pass in the folder path so at that basic point there if I were to simply run this subroutine by pressing f5 it will generate a new folder on my desktop if I run it again it will delete the folder and then recreate it it might be better to step through this just to prove that it is working so I can delete the folder and spheres then recreate it from scratch and I've always got our folder available now to write these text files into okay the next little bit of setting up to do is to count how many columns that are in my data set so when I'm leaping over these these individual rows I want to make sure that I'm writing out every column of data to the text file so simple convenient way to count how many columns are are which is my calls variable and make it equal to range pay one current region which refers to the entire block of data the entire table and then I can refer to the columns property that and then simply count them that will help me out a little bit later on as I'm looping over the files sorry back on looking for the film's to generate the files so what we can do now is begin the loop so I can say for each are in not sleep of every cell from range a - down to the end of this list some one convenient way to achieve that say from range a to comma and then to reach range a whatever a 1200 and one ish I can go from cell a1 end Excel down to find that cells I can say range a one go from the top of the list dot end Excel down the reason I'm excluding the the first row I'm not using ranging a one here so I don't want to write out the title cell anywhere the title row into the text file I just want to write out the individual films so close two sets of parentheses to wrap up that statement give myself a few blank lines and then say next are to move on to the next row and then what I can do is start building up the file name for each individual cell in this list now to generate the file name to begin with all I'm going to do is say file name equals R value ampersand and then concatenate a dot txt DM or quite tab delimited files for these these individual films what a magnum of you is attempt to open up a text file with that specific name I'm going to make sure that if the text file doesn't actually exist then it will be created automatically so to do that I'm going to concatenate the folder path and a backslash character whatever values can be stored in the file name variable so to do that I can say set T s equals FS o dot open text file and open up some parentheses I can then concatenate the folder path with a literal backslash character and then with the file name variable so file a name I want to make sure that I'm opening up the file to append new data to it rather than to read data from it so you need to choose the for appending option for the i/o mode parameter then I also need to specify that if the file doesn't exist that I want to create it so I can do that by setting the create parameter to true close the parentheses at that point and then I'm going to write out some data to that text file but I'm not going to write out that language yet once I finish writing data albums your data quick comment right to tax filers and a problem to solve first write to text and then what have done nothing on a closer text file and then set T s to be equal to nothing and then move on to the next row in the dataset to write it out to another text file now if I were to attempt a room this ability at this point I'm going to hit a problem fairly quickly and you'll see that I've got an error message here a runtime error that file name or number if I click debug is of course the line is trying to open up a text file in this particular case it's the one that's trying to open up Who Framed Roger Rabbit question mark hint hint dot txt so got a problem with a file name here there's got a question mark symbol in it I've actually inadvertently created a few other problems for myself as well I just stop or reset that procedure and look back at my desktop if I double click into my films folder there and then have a quick look inside here you see I've got a huge number of text files here some of which look very sensible but there are a few odd ones in there that don't look quite the way they should be down to dot txt extension and not listed as text documents so for instance Master and Commander and mission I don't remember a film called mission in my list Pirates of the Caribbean I don't remember a file a film just called Pirates of the Caribbean they all had subtitles so that one there if I would say look if I just close that window down actually have a look back in my Excel list types of the Carribean : at World's End so I've clearly got a few issues here with with some certain characters in my film names and the reason for that of course is that there are a set of illegal characters for file names characters you're simply not allowed to use if you want to know what the exact list is it's a fascinating article I don't really mean fascinating and on the Microsoft web site naming files Pat's and namespaces and in here is a little section about illegal characters so it's just that little list there's not that many but you see that there's a colon character is one of the problems that question mark characters another they generate different problems of course you can see that the colon didn't throw a runtime error it Catholic generated files that they weren't the exact file name that I wanted the question mark did throw a runtime error because question mark similar can't be contained in the file name misused as a wildcard character in there in Windows operating systems so and what we need to be able to do is check if any of these characters exist we need to replace them with I suppose nothing or maybe a space or some other legal character so let's look and have a look at how we could achieve that now one fairly simple approach to solving this problem will be to just write a separate replace statement for each individual character that we wanted to replace in the film's name so that's kind of a bit boring a bit tedious I'm not very satisfying either so we're going to take a slightly different approach what we're going to do is create an array we could get also use a collection or a dictionary I suppose isn't really matter but I think an array would work best for this particular example and that way is going to contain that this list of characters and then for each filming we encounter we're going to loop over the array contents and we try to replace each different string inside the file name so to get started let's head back to the VB editor and inside the same subroutine we were just working with we're going to declare a couple of new variables so get this up we're going to say dim I'm gonna call my illegal characters or illegal chars for short so illegal chars and is going to be an empty array with no type well I can then do as well because I'm going to be to loop over the array later on I'm going to declare a variant parade so if I'm a variant variable so dim V as variant I might as well then just populate the illegal chars per array right now so I can say illegal chars equals then there's an array function in VBA that allows you to build up essentially a list of as many different strings or numbers or dates or references that you want so you can just write them all out from scratch so a quick look how many we got we've got one two three four five six seven eight nine all together so I need to pass in nine different individual strings so each one is going to get passing into side a set of double quotes so I'm typing two double quotes and then a comma then I'm going to copy those and then paste them in eight times so one two three four five six seven eight if I take away the last comma and then replace that with a closed parenthesis that gives me a slightly easier job now filling in the individual characters let me just change the width of my BBFS as I can see them a little more clearly so the first one is a less than character so I can type in a less on character then a greater than then a colon of course we've seen about a problem gives us a problem then there's a double quote care about slightly interesting and double quotes if I type in a single double quote and then click away from that line like giving you a rimfire so a syntax error so the reason for that is to insert a single double quote character as on a literal character you have to double them up in VBA so double close double quotes double quotes double quotes 4 of them all in a row will give you a single double quote character a spendable code word i've got we've got a forward slash character then a back slash character then a vertical bar or pipe shift and backslash and my keyboard then a question mark as we've seen we know that one gives us a problem and then finally the other wild-card character an asterisk or a star okay so that's all of those strings all those individual characters stored in my array let's just change the width of the screen again to make it a little more readable what we need to do now is inside our main loop for each new file name that we encounter we want to loop over the contents of the array and replace each character with an empty string let's just make a quick change to this line first I'm going to take away the concatenated file name extension at that point to begin with I just want to store the the film name in the file name variable what I'm then going to do is use my variant variable to loop over the contents of the array so I'll do it in a fairly simple way using a for each loop so for each V in illegal chars give myself a couple of blank lines in those saying next V and for each one of those what I would like to do is set the file name to be equal to the replaced version to file now to replace file name and I'm going to look for the contents of the variable so whichever individual character I'm looking at to start with I want to replace that with another shrink so open some double quotes close some double quotes I could put in a space if I prefer or some other legal character but I think an empty string would work best for this particular example if I close the parentheses at that point just after I finished looping to make sure we add on the final extension to the file name let's say file name equals itself ampersand opens in double quotes dot txt close double quotes okay so at that point we should be already ready to give us a quick test let's just run the subroutine and it might feel a little tiny bit longer to remember made it before it seems you've happened fairly quickly and if we switch back to the desktop and have a look into this films folder we've got a separate text file for every individual film so one dozen 157 different text files and the setting is there isn't really anything contained in those text files at this point so the last step of the entire process is to make sure that we write out the contents or the details for each film for that particular files I just close down my window we have batch the VB editor and wrap things up with us this last part now in order to do this I'm going to use the same technique I used in the previous video in this series which is talking about joining strings some use the join function and the transpose function to to achieve what I'm trying to achieve here so into these Mike : tear right to tech so I'm going to say TS white line which will write an entire line into the text file of course I'm going to give myself a space underscore to move on to the next line because I need to write a relatively long expression to make this work so going to the join function which we covered in detail in the previous video so I'm not going to am I'm not going to explain how this works right now have a look at the previous video on the absolute detail of this but basically the join function requires some sort of array which you don't specify the delimiter for and we'll take the contents of the array and join it into a single continuous string so I could do completely pointless to this point of course if I could refer to my illegal chars array and then say separate them with a tab character it would put all of these individual strings into a single continuous one that's not what we're trying to do of course what we're trying to do is take the contents of a row of data from the worksheet so I can refer to the first cell of that rope with a letter R and then I can refer to the rest of that row by reciting it so I'm going to resize then I can open up some parentheses and I can specify the number of rows I want which would be one and the number of columns that I want which will be equal to the number stored in my calls variable from early on so I say coals then close the parentheses and say dot value now the problem with that is that unfortunately that's reference to that range of cells doesn't return an array that can be passed into the join function I also want to specify the delimiter character for the join as well so just after the the value of M is I'm going to comma followed by I'm going to go for the VB tab doing tab delimited files so um the problem that I've got as I was mentioning is that this isn't the valid value to pass into the joining function I need to convert this into a single dimensional array and currently says double dimensional two dimensional array so the way that you do this slightly bizarrely just go with it for now and again the previous video gives the details of this I'm going to say application dot transpose r dot resize one calls value and then I'm going to transpose that again so I'm going to transpose it twice which will convert the two-dimensional array into a single dimensional array so I'm going to copy and paste application doc transpose and then just make sure that I close the correct number of parentheses and having done that what I can finally do is run the entire procedure we will get rid of the old file and generate a new folder with all those files shouldn't take too long finally if I now go back to the films folder give that a quick double-click I will have a whole bunch of different files with different bits of information in them some more heavily populated than others of course so let's have a quick look if I can find something like let's find one Anna Karenina and I know that one's got quite a lot of different versions let's look better there she is so there's four different versions of that film in there and likewise King Kong and various other examples too so slightly more complex actual practical example of using the replace function but hopefully can see that all the techniques the basic techniques we were looking at back in module 1 a relevant and important for this slightly more complex practical use hope you found that one useful anyway thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials leaving some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 15,974

Rating: 4.9407406 out of 5

Keywords: vba, excel, replace, substitute, string, text, text file, tab delimited, case sensitive, proper case, wise owl

Id: qvxmHbrwYmk

Length: 39min 43sec (2383 seconds)

Published: Wed Feb 22 2017