1

I have a field where the name will be stored as "Account A acquired Account B". Now from this field, I need the word to be split in two parts for two different field. one is before acquired and one is after acquired. It will be like field 1: Account A, field 2: Account B

I used the following formulas but it isn't giving me correct output.

field 1 formula:  IF(CONTAINS(Name, "acquired"), LEFT(Name,Find("acquired", Name)-1), '')

field 2 formula: IF(CONTAINS(Name, "acquired"), RIGHT(Name,Find("acquired", Name)), '')

Can anyone please suggest changes in the above formulaes

4
  • 1
    Can you add the erroneous output you're getting? Commented Apr 26, 2020 at 7:49
  • If the name is like ABC Corp acquired DEF Corp, I'm getting the output as ABC Cor for one field and red DEF Corp for second field. The account name lengths might vary but I need the string to be cut before and after acquired word in the sentence. Using the given formulae, I cannot able to predict the proper outcome
    – Skumar
    Commented Apr 26, 2020 at 7:55
  • I understand what you’re trying to do. I cannot understand what error you’re encountering. Please update your question m Commented Apr 26, 2020 at 7:59
  • @SebastianKessel, Its not error but I need a correct formula where I can exactly divide the string before and after "acquired" word in the name field irrespective of field length
    – Skumar
    Commented Apr 26, 2020 at 8:02

1 Answer 1

3

I'm not sure why your first formula is cutting off the last character of the firm name - it works for me. In fact, you should subtract 2 from the FIND() result to remove the trailing space (but using -1 will display fine, just with a space after the name):

IF(CONTAINS(Name, "acquired"), 
    LEFT(Name,Find("acquired", Name)-2), 
    '')

For the second formula, you need to account both for the overall length of the string, and the fact that FIND(Name,"acquired") returns the position of the "a" in "acquired":

IF(CONTAINS(Name, "acquired"), 
    RIGHT(Name,LEN(Name)-Find("acquired", Name)-8), 
    '')
1
  • Thanks @Thomas, it worked :)
    – Skumar
    Commented Apr 27, 2020 at 8:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .