PiedPiper Posted January 3, 2004 Share Posted January 3, 2004 I would appreciate help with this problem. We had one data entry person who put spaces between the city and state like Sacramento, (8 spaces) CA. Major bummer. It looks terrible. She was trying to line the state up with the words STATE right above it. She has been stopped but I want to correct the mistakes. How can I remove all those extra spaces and just leave one between the comma after the city and the state? I also tried changing field validation so it only allows 1 space by using PatternCount but I can't get that to work based upon where the comma is. I can't even get out of the box at all. Something about parenthsees wrong. oh. And our new program will have city and state in different fields but that is months away yet. Until then, I need to stop the spaces. Sometimes also they don't put a comma between the city and state. Can I force them too? Because if they don't put the comma, the validation on spaces won't know where to start (I don't think). You guys have helped so much in the past. I hope you can help me again. Pete Link to comment Share on other sites More sharing options...
Lee Smith Posted January 3, 2004 Share Posted January 3, 2004 Hi. Use the Substitute function. Either by first placing your cursor in the field, and then going to the "Menu - Records - Replace - by Calculated value," and put this calculation in the calculation box: Substitute(YourCityStateZipField, " ", "") Or, Make it a script step Replace Script Step and your field and the calculation above. HTH Lee Link to comment Share on other sites More sharing options...
PiedPiper Posted January 3, 2004 Author Share Posted January 3, 2004 Hi Mr. Lee Smith. It worked fine. But it removed all the spaces. Now I need to put one back between the comma and the state. Any suggestions? I think I could just reverse what you gavae me but how will it know where to put the space? Link to comment Share on other sites More sharing options...
DanBrill Posted January 3, 2004 Share Posted January 3, 2004 Assuming you use Lee's first method, you'll have to do it one more time replacing "," with ", " to get the space back after the comma. While you're at it, consider breaking the city and state into two fields. It's better database practice to keep your fields "atomic," meaning that, like an atom, they can't be split up into meaningful parts. Given recent advances in sub-atomic physics, maybe we should change the phrase 'atomic' to 'quark-ish' or 'stringy.' Dan Link to comment Share on other sites More sharing options...
PiedPiper Posted January 3, 2004 Author Share Posted January 3, 2004 ok i got the space back thanks Dan. I plan to have city and statte in separate fields but for now, they must stay. The existing program has relationships based upon this field (I know, I know, I didn't do it) and also searching and, well,many things depend upon it. If I tried to change it now I'd mess up everything else in the system. Several files have been converted to a new program that I put together but Advertising has not. And Advertising has its own customer list also. Very bad, I know. It will be hell merging two customer lists when time. Can I stop extra spaces in field validations? And can I insist that a comma be put between the city and state? Otherwise, my prospect letters and evenlopes don't print right. I considered using a script to correct all data between each mail out but that would take forever to run through so many records to correct every time someone enters something. I just dont have the time to change this file just yet. I need to use duct tape for another few months at least. Link to comment Share on other sites More sharing options...
Lee Smith Posted January 4, 2004 Share Posted January 4, 2004 Hi Pete, First of all, I apologize for the error in my calculation. I also should have warned you that the Substitute Function is one of those that you can not UNDO if it doesn't work, and that you should test this on a duplicate of your file or a make a little test file, to ensure that it does what you want. I agree with Dan, that this might be a good time to separate this data out this into separate fields, if possible. There have been several posts on this subject in the past, so a search of the forum using Extract or Parse and the words City, State and Zip should lead you to an answer. If you need help in this, please let us know. HTH Lee Link to comment Share on other sites More sharing options...
DanBrill Posted January 4, 2004 Share Posted January 4, 2004 Try this: Assume your field is called CityState. Go to field options. Select Validation. Select validate by calculation and use this: PatternCount(CityState,",")>0 and PatternCount(CityState," ")=0 This will ensure that there is a comma and only one space. Dan Link to comment Share on other sites More sharing options...
DanBrill Posted January 4, 2004 Share Posted January 4, 2004 By the way, that's 2 spaces in the PatternCount(CityState," ")=0 term. Kinda hard to tell, esp. with a proportional font. Dan Link to comment Share on other sites More sharing options...
PiedPiper Posted January 4, 2004 Author Share Posted January 4, 2004 Hey Lee! Don't apologize. You removed approximately 1200 random spaces from the field. I just had to put ONE back. -) What you both say is true of course. But I only have so much time. I would have to redo several scripts (mostly seaches) and two relationships to change it right now. And I plan on [censored]-canning this whole file in a few months and combining it with my customers with a related file of activities. THAT file has city and state in their own field. Just can't do it right now. But I don't want 7,000 letters&evenlopes generated in the next two months to go out with spaces and without comas. Thanks for the calculation Dan. I'll try it and let you know but I'm sure it will work for me. :-) I guess i shouldnt be concerned if they do it again because at least now i know how to fix it. But the proposals could be sent before I catch it. :-( i pray nothing else needs to be done in this mess before i move the data. as it is, the city/state fields that need to be fixed have relationships based upon thosee wrong keys. Yeah, I know. Atomic isnt the word that comes to my mind, Dan. this file is nova (expanded like urban sprawl) - 8 years of every person working here changing the fields, etc most with no idea what they were doing. And I'm not much better at all.and there are calculations based upon other calculations based upon...yep. we (not so) affectionately call it DAWG. Pete Link to comment Share on other sites More sharing options...
BobWeaver Posted January 4, 2004 Share Posted January 4, 2004 This formula will convert multiple spaces (up to 460 in a row) into a single one: Substitute(Substitute(Substitute(Substitute(Substitute(txt, "xxxxxxxxxxxxxxxxxxxxx","x"), "xxxxxx","x"), "xxx","x"), "xx","x"), "xx","x") In the formula shown here, x's are used for clarity; replace them with the same quantity of space characters: 21, 1 6, 1 3, 1 2, 1 2, 1 Link to comment Share on other sites More sharing options...
RodinBangkok Posted January 4, 2004 Share Posted January 4, 2004 The nested substitute approach BobWeaver shows is best if you know you have a finite number of spaces to deal with, as this can be implemented as a calculated field without the use of scripts. If however you have an unknown amount of spaces or other characters to deal with the following script will take care of an infinite amount. Loop Set Field [ Substitute(YourField, "xx", "x") ] Exit Loop If [ PatternCount(YourField, "xx") = 0 ] End Loop Rod Link to comment Share on other sites More sharing options...
BobWeaver Posted January 4, 2004 Share Posted January 4, 2004 Well, hopefully the unknown number of spaces won't exceed 460 contiguous. A loop is not necessary. The formula I gave will strip any number from 2 to 460 without needing to modify the formula. And if you need more, then you can nest an additional substitute function inside with a search string containing 231 spaces and the replace string containing one space. Then, this resulting formula will strip any number of spaces from 2 to 53590 inclusive. So, the resulting formula would be: Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(textfield, "<231 Spaces>","<1 space>"), "<21 Spaces>","<1 space>"), "<6 Spaces>","<1 space>"), "<3 Spaces>","<1 space>"), "<2 Spaces>","<1 space>"), "<2 Spaces>","<1 space>") But, this is likely overkill in most situations. Link to comment Share on other sites More sharing options...
LaRetta Posted January 5, 2004 Share Posted January 5, 2004 Agreed. It is probably overkill to account for that many spaces. But I have to admit I love the theory that Rod presented, ie, continually looping until only one space remains - which can be applied in many other instances of removing other duplicate data once I think of them - and in many other applications as well. It tickles me. Bob again, as always, you've provided a very pretty solution because this could be used to stop all new multiple-space entries, regardless of how many (pretty much). But I wonder how he plans to remove these spaces originally. I don't suggest Replace Contents if he's networked. Pete, Bob's calculation can be placed within a loop to address a found set to remove the miscreant spaces for you as: Go To Record/Request/Page [First] Freeze Window Loop Set Field [YourField, Bob's calculation] Go to Record/Request/Page [Next, Exit after last] End Loop With Rod's you would need to nest a loop within the loop to loop through records (whew!). It would look like: Go To Record/Request/Page [First] Freeze Window Loop Loop Set Field [YourField, Rod's calculation] Exit Loop If [PatternCount(YourField, "xx") = 0] End Loop Go to Record/Request/Page [Next, Exit after last] End Loop I am unsure which would be the quickest. Probably Bob's because it only sets each records field once. LaRetta Link to comment Share on other sites More sharing options...
RodinBangkok Posted January 5, 2004 Share Posted January 5, 2004 For the problem originally in question the use of nested substitutes, I'm sure, is the best recommended solution for removal of operator entered spaces or extra characters, as Bob indicates who is going to sit there and tap in more than 460 spaces. I use the loop during import of large text files, these are generated by different types of factory process control equipment and can contain a lot of different characters in large quantities. For these rather than continually modiying a calculation field I have several of these loops set up and instead of hard entering the character in the script it is placed in the script in the form of a global variable so these can be set up by the user during import of the files with no modification of the fields or scripts. I suppose I could have set up the same system using nested substitutes in a calc field, but as I do this during import, running the script at that time on one record works fine. For me if I were going to do a one time filter of a database file, the script approach would be transparent to the structure of the database, meaning no need to generate new fields, and the script can be deleted after the operation. If however the filter is to be done all the time a calculated field would be the best permanent solution. Link to comment Share on other sites More sharing options...
LaRetta Posted January 5, 2004 Share Posted January 5, 2004 Hi Rod, That's what excited me! I realised the power of using a global(s) to hold various funky characters - CRs, spaces, periods, text, and more! What a wonderful way to 'substitute' generically. How many calculations and scripts do we/have we all written (and modified) to clean up imported data, data migrate or even correct our Users data-entry? If they weren't hard-coded but instead stored in globals, we would have flexibility and less calculations and scripts. We could more focus on just a few 'generic' calculations and scripts (and finds). And your suggestion of looping on the same record to replace (whatever) with (whatever) until those (whatever) are (whatever) made me realise I could run a SET of globals containing anything I wanted through the same record to switch out data in a sequence by looping the same record until the PatternCount() test = 0 (which could be a result in another global). I envision... One PatternCount() calculation (containing one or more global fields) will produce a flag = 1. Then, upon execution of script (which would start with Exit Record Request to update the calculation), the miscreants would be found by this generic Find script. The Characters wouldn't have to be hard-coded. These scripts should be separate and then they could run on one record or it could be Performed[sub-script] from within the loop for a found set. And scripts could be easily duplicated to apply to other fields. It throws open doors of possibility for me. Thanks everyone for a great FM feeding today. LaRetta Link to comment Share on other sites More sharing options...
RodinBangkok Posted January 5, 2004 Share Posted January 5, 2004 LaRetta, as FM is a nice stable cross platform app, I've used it for text conversion routines to convert one type of text file to another, mostly for special machine applications. I found it easier/quicker than writing Basic programs to do the conversions. One recommendation, when you set up these conversions transfer the field you are working on to a temporary field, that way its easy to perform an Undo, just copy the original field again into the temporay field. Rod Link to comment Share on other sites More sharing options...
PiedPiper Posted January 6, 2004 Author Share Posted January 6, 2004 Thanks. I ended up using the scipt to loop that La retta suggested and used Bobs first formula. I ended up trying everything here I just couln't resist trying the ideas. You guys rock! Sorry, and gals. But something I did wrong because Bob's second formula wouldn't do anything. I copied it from here and pasted it and change to my field name and it looked like it worked but didn't remove any spaces. I copyied it back here. I think this would be the ultimate protection for fields, right? then that's what I would like to use but something is wrong. Substitute( Substitute( Substitute( Substitute( Substitute( Substitute(CityState,"<>","<>"),"<>","<>"),"<>","<>"),"<3 Spaces>","<>"),"<>","<>"), "<>","<>") I would really like to use this in every field to stop people from spacing too much. the same script worked with the first formula but not this one. I even counted the substitutes and its the same. I even clicked my feild instead of typing it. well, when previewd all the spaces are gone but they were there, really they were. 200 of them. I just checked and I i had the right number of spaces. It took time to count them but they were all there. oh I had 3 spaces listed in the one part so that's probably why it didn't work. It was so long that I jdidn't see it. But I just tried it with spaces there instead and it still wont' work right. I know you wouldn't steer me wrong Bob so I'm doing something dumb here. but I really checked it and re-counted the spaces inside it. ok i only counted the spaces in the first one, but I made sure I carefully entered the others. Pete Link to comment Share on other sites More sharing options...
BobWeaver Posted January 6, 2004 Share Posted January 6, 2004 Here is the formula using x's instead of spaces. I can't post the real one using spaces, because the forum software removes excess spaces. Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(textfield, "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","x"), "xxxxxxxxxxxxxxxxxxxxx","x"), "xxxxxx","x"), "xxx","x"), "xx","x"), "xx","x") The innermost one has 231 spaces in the search string. Maybe you had included the < and > symbols? I was just using them as angle brackets but didn't intend them to be included in the formula. Link to comment Share on other sites More sharing options...
-Queue- Posted January 6, 2004 Share Posted January 6, 2004 That's why I always cheat: Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(textfield, "[color:"white"]_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________","[color:"white"]_"), "[color:"white"]_____________________","[color:"white"]_"), "[color:"white"]______","[color:"white"]_"), "[color:"white"]___","[color:"white"]_"), "[color:"white"]__","[color:"white"]_"), "[color:"white"]__","[color:"white"]_") Link to comment Share on other sites More sharing options...
PiedPiper Posted January 6, 2004 Author Share Posted January 6, 2004 Wow that sure stretched the colume out. I had to search for the reply button. Yeah, Bob. You said, "I can't post the real one using spaces, because the forum software removes excess spaces." I found that one out myself also. And i had left those brackets in the formula. I thought they were exponents or intigers or some other mathmatical compilation to arrive atthe complex answer. actually Queue's suggestion would have helped when i cut and pasted it cus when you said spaces, and i removed the underlines, spaces would be all that was left. thanks. But i still would have needed the numbers listed out, Bob. 231 spaces isn't easy as you know since you did it for your answer and then did it again using x's. thanks for taking the time to do that fr me. Now on to other problms with archivng this stuff. at least I now have someone to ask. Happy tides! Pete Link to comment Share on other sites More sharing options...
-Queue- Posted January 6, 2004 Share Posted January 6, 2004 I should also note that if you copy and paste with the 'white' text, as above, you should first paste into a text editor (or Word/AppleWorks) and Find/Replace the _'s with a space. And I could have used x's but a replace would have changed "textfield" into "te tfield". Whoops! Link to comment Share on other sites More sharing options...
BobWeaver Posted January 6, 2004 Share Posted January 6, 2004 A formula could be posted verbatim if the forum code tags worked. But, they quit working a few months ago after an update. Haven't checked lately to see if they're working yet. Let's have a look: This is a test with 10 spaces: " " Link to comment Share on other sites More sharing options...
BobWeaver Posted January 6, 2004 Share Posted January 6, 2004 Yippee, it works again! Link to comment Share on other sites More sharing options...
underhat Posted January 11, 2004 Share Posted January 11, 2004 BobWeaver said: this resulting formula will strip any number of spaces from 2 to 53590 inclusive. So, the resulting formula would be: Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(textfield, "<231 Spaces>","<1 space>"), "<21 Spaces>","<1 space>"), "<6 Spaces>","<1 space>"), "<3 Spaces>","<1 space>"), "<2 Spaces>","<1 space>"), "<2 Spaces>","<1 space>") Hi BobWeaver, I'm trying to figure out why this particular sequence of numbers works. Looks like it's something to do with triangular numbers? Something with which I'm not very familiar. Do you know the math behind this? I'm curious Link to comment Share on other sites More sharing options...
BobWeaver Posted January 12, 2004 Share Posted January 12, 2004 Well, a few weeks ago when I had nothing better to do, I decided to work through the math to figure out the optimum way of nesting the Substitute functions to maximize the number of spaces that could be removed. Here is what I came up with: http://www.fmforums.com/threads/showflat.php?Cat=&Number=86335&page=1&view=collapsed&sb=5&o=365&fpart=1 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7404 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now