• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

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.
0
pdvsa
Asked:
pdvsa
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
pdvsaProject financeAuthor Commented:
Wow.  That worked.  Thank you for the help!  Have a good night!
0
 
Gustav BrockCIOCommented:
Thanks!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now