Concactenate - remove duplicate words

Experts, I am concactenating 2 fields.  Sometimes a word is duplicated though.  In the below, you can see the result of the concactenate and the duplicate:  "Monthly - Monthly".

Concactenate result:
Monthly - Monthly no later than the 10th of the Month.
I need this though: (remove the 2nd occurrance of the word "monthly")
Monthly - no later than the 10th of the Month.


here is the formula in the field:
=[Frequency] & " - " & [timing]

How can I prevent the duplicate?  I am looking for simple solution I can use in the field preferably.
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neeraj SoniSr. ArchitectCommented:
You might check with :-
   Replace ( string1, find, replacement, [start, [count, [compare]]] )
   Len( text )

Replace Frequency text with blank in [timing] text starting from position 1 till the length of [Frequency]

Something like this :-
   [Frequency] & " - " & Replace([timing], [Frequency], " ", 1, Len(Frequency) )


I've not tested it but you might want to give it a try and tweak it.

Use Trim() if there are spaces in any of the texts.
0
Rey Obrero (Capricorn1)Commented:
how are you concatenating the words?
0
Neeraj SoniSr. ArchitectCommented:
I guess & is used for concatenation.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rey Obrero (Capricorn1)Commented:
of course, what else are you going to use to concatenate,
 but what I mean is, if he is using a function to do the concatenating. Post the function here.
0
pdvsaProject financeAuthor Commented:
Rey,
<how are you concatenating the words?
==>It is in a report and I am using =[Frequency] & " - " & [timing] in a field in the detail section of a report.   I hope that answers your question.

Neeraj:  that is very close.  I need to ask a follow up. could the solution be adjusted to only strip out the word if it is at the beginning of [timing] only?  

Example:
You can see below, the word "monthly" is stripped out but I would like to limit it to the beginning of [timing] only  
original:    Monthly - Continuous (monthly payments) through the remainder of the term
modified: Monthly - Continuous (  payments) through the remainder of the term
                                                      ^^^^stripped 'Monthly' but since it is not at the beginning of [timing] I don't want it stripped

here the solution works perfectly:
original:   Monthly - Monthly no later than the 10th of the Month.
modified: Monthly - no later than the 10th of the Month.
0
Gustav BrockCIOCommented:
This expression will do:

    =[Frequency] & " - " & Replace([Timing],[Frequency] & " ","",1,1)

/gustav
0
pdvsaProject financeAuthor Commented:
Hi Gustav,

After scanning the data, I have noticed I might need a tweak.  If [Frequency] and [Timing] are both equal to each other then I only need the [Frequency].  Below you can see the result "Monthly" is shown two times because the values in Frequency and Monthly are both equal to each other.  

Example:
Monthly - Monthly
would like to return only:
Monthly
0
Gustav BrockCIOCommented:
OK. Then you probably can get away with:

    =[Frequency] & IIf([Frequency]=[Timing],""," - " & Replace([Timing],[Frequency] & " ","",1,1))

 /gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Wow.  That worked.  Thank you for the help!  Have a good night!
0
Gustav BrockCIOCommented:
Thanks!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.