1 2 Previous Next 18 Replies Latest reply on Jan 29, 2016 9:39 AM by Mike_Mitchell

    How to Find & Replace Name (Easy?)

    osensnolf

      I have a field where the Company name is stored.  In it, I want to replace the abbreviation "Co" with Company.

       

      For instance, I want "The Construction Co" to become "The Construction Company".

       

      I know how to do Find & Replace, but how do I replace "Co" with the word "Company" without changing the "Co" in "Construction" to "Companynstruction"?

       

      I know how to do a search for =Co and =Co. and make that the found set but I'm not sure how to change Co to Company.

       

      Any idea?

       

      I tried "Co" but that doesn't work.

       

      Thanks

      Steve

        • 1. Re: How to Find & Replace Name (Easy?)
          beverly

          find by the end of the previous word AND the one:

           

          find ( "tion Co" ) replace ( "tion Company" )

          beverly

          • 2. Re: How to Find & Replace Name (Easy?)
            osensnolf

            This has merit but isn't a solution.  It would only work if I only have a few other words that start with "Co" in the company name.  But I have many.  Hundreds if not thousands.

             

            Cooper Construction Co

            Mike Connor Building Co

            Awning & Cover Co

            etc...

             

            The ideal option would be if there was a way to only replace Co with Company if Co is the entire word.

            • 3. Re: How to Find & Replace Name (Easy?)
              user19752

              How aoubt appending "mpany" ? For "Co." you need to remove "."

              • 4. Re: How to Find & Replace Name (Easy?)
                Fimano

                It seems the " Co" string is always at the end? If not, it gets slightly more complicated. I would write a custom function. The function should replace a trailing " Co" (no period) with " Company". On the field level, you could set CompanyTO::NameField to have an Auto-Enter calculated value, in which you specify your custom function. Remove the tick/checkmark in "Do not replace...". In the future, whenever you enter a name ending in Co, it will end in Company as soon as you tab out of the field. Test if this is the case by creating a record which you just revert. My CF, quickly put together, is: CompanySpellout ( text ) = Case ( Right ( text ; 3 ) = " Co" ; Left ( text ; Length ( text ) - 3 ) & " Company" ; text ) Remember, replacing only takes place when a field is modified, i.e. when you create/edit a record. You could do a search like described above to find the company records ending in " Co". It is a great idea to browse through the found records and watch the Data Viewer (provided you use FMPA). MAKE A BACKUP or EXPORT FOUND SET TO XML, then put your cursor in the field and Replace Field Contents (from the Records menu); replace with a calculation using your CF, and just specify the field name as the result. Hope this helps.

                • 5. Re: How to Find & Replace Name (Easy?)
                  Fimano

                  Hmm, this form ate my format...

                  • 6. Re: How to Find & Replace Name (Easy?)
                    Mike_Mitchell

                    What about:

                     

                         Case (

                     

                              RightWords ( companyName ; 1 ) = "Co" ;

                              LeftWords ( companyName ; WordCount ( companyName ) - 1 ) & " Company" ;

                              companyName

                     

                         )

                    • 7. Re: How to Find & Replace Name (Easy?)
                      nicolai

                      To find through the standard search you can try to use "==* Co" without quotation marks.

                      Unfortunately, I can't get Find/Replace to work with search operators

                       

                      Another option to consider:  click into the company name field and and select "Records"/Replace Field Content" from the top menu.

                       

                      One of the replacement options is replace with calculated result. You can try to use something like this (assuming the name of the company field is called CompanyName):

                       

                      If (

                         Right(CompanyName; 3) = " Co" ;

                         Replace ( CompanyName ; length(CompanyName)-2 ; 3 ; " Company" );

                         CompanyName )

                      • 8. Re: How to Find & Replace Name (Easy?)
                        osensnolf

                        For the most part, "Co" does appear at the end but often it is also seen as "Co, LLC" or "Co, Inc."

                         

                        I've continued looking and I think I found a solution in another program.  I will export my record ID and my company name.  The software breaks the company name into individual words and allows me to replace exact words with something else.  In this case, it will allow me to replace all exact words of "Co" with Company.  It's a couple extra steps and takes longer but I think this will be the only way to get exactly what I want as there are words other than Co that I need to revise.

                         

                        Thanks for the help.  If someone does find a surefire way to do this, let me know.

                        • 9. Re: How to Find & Replace Name (Easy?)
                          Mike_Mitchell

                          There are ways to do this FileMaker-native, but if you have an external solution, it might be easier just to go with that.

                           

                          However, for the purposes of familiarity, you can use a calculation like:

                           

                          Substitute ( companyName ; " " ; "¶" )

                           

                          to break your text string into values. Then you can use the various Values functions ( LeftValues, MiddleValues, RightValues, GetValue) to manipulate the result.

                           

                          Oh, and there's an Exact function, too ...

                          • 10. Re: How to Find & Replace Name (Easy?)
                            beverly

                            I have often needed to do this for clients where the entry needed to be massaged & FMP could work, but take excessively long.

                             

                            Export with the unique id and the value to change does allow "import-back-matching-id" UPDATE of the records!

                             

                            beverly

                            • 11. Re: How to Find & Replace Name (Easy?)
                              osensnolf

                              My thought exactly.  This will allow me to make some other modifications and clean up some things that takes Filemaker much longer to do than some other programs.

                              • 12. Re: How to Find & Replace Name (Easy?)
                                beverly

                                Yes! like those places where there is ONE name field and/or ONE address field. LOL!

                                beverly

                                • 13. Re: How to Find & Replace Name (Easy?)
                                  Extensitech

                                  Substitute ( Trim ( " " & CompanyNameField & " " ; "Co" ; "Company" ) )

                                   

                                  HTH

                                   

                                  Chris Cain

                                  Extensitech

                                  • 14. Re: How to Find & Replace Name (Easy?)
                                    phil-hanson

                                    Filemaker's Find/Replace has a Match Whole Words Only option, Wouldn't that do the trick?

                                    1 2 Previous Next