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

Improve this question
4
  • 1
    Can you add the erroneous output you're getting?
    – Sebastian Kessel
    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
    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
    – Sebastian Kessel
    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
    Apr 26, 2020 at 8:02

1 Answer 1

Reset to default
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), 
    '')
Improve this answer
1
  • Thanks @Thomas, it worked :)
    – Skumar
    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 .