function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Juhi Dhiver 5Juhi Dhiver 5 

Fetching strings from the middle of the Item Name

I only want the third word from the strings, I am stucked with the third formula , can anybody help me out...Here what I am trying : IF( NOT( ISBLANK(Name)) ,RIGHT( Name , ((LEN(Name) - FIND("", Name)) - FIND("",Name))  , MID(Name,Find(" ",Name,Find(" ",Name,1)+1)+1,Find(" ",Name,Find(" ",Name,Find(" ",Name,1)+1)+1)-(Find(" ",Name,Find(" ",Name,1)+1)+1)))
But not getting correct O/P ,when Name contains only three words. It work well when Name has four word in it.
Please help me out with the formula.
 
Best Answer chosen by Juhi Dhiver 5
MellowRenMellowRen
Lynn is correct in that this would be far easier and cleaner using an Apex based solution (a Before Trigger)—furthermore an Apex solution could be designed to counter-act bad data entry like double spaces. The best you can do in formulas is strip leading and trailing spaces with the TRIM function.

That being said, if it has to be a formula, this rather ugly one will do as you ask: 
IF( ISBLANK(Name), "<Blank>",
    IF( FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )=0, "<None>",
        LEFT(RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )),
               IF( FIND(" ", RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )))=0,LEN(RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 ))),FIND(" ", RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 ))))
            )
       )
  )
I have made it produce the “error” values of <Blank> for an empty field and <None> if Name only contains 1 or 2 words. Replace these with whatever is appropriate for your needs. The rest correctly grabs the third word regardless of whether it has 3, 4, 5, … words in the Name—assuming no oddities like double sapces, hard spaces, non-printing characters, etc.

Good luck.

All Answers

Lynn M GrandeLynn M Grande
Did you try using Stinr.split and just grab the third word....i.e. split by space...
https://foobarforce.com/2013/08/30/apex-method-of-the-day-string-split/
 
MellowRenMellowRen
Lynn is correct in that this would be far easier and cleaner using an Apex based solution (a Before Trigger)—furthermore an Apex solution could be designed to counter-act bad data entry like double spaces. The best you can do in formulas is strip leading and trailing spaces with the TRIM function.

That being said, if it has to be a formula, this rather ugly one will do as you ask: 
IF( ISBLANK(Name), "<Blank>",
    IF( FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )=0, "<None>",
        LEFT(RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )),
               IF( FIND(" ", RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )))=0,LEN(RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 ))),FIND(" ", RIGHT(TRIM(Name),  LEN(TRIM(Name)) - FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 ))))
            )
       )
  )
I have made it produce the “error” values of <Blank> for an empty field and <None> if Name only contains 1 or 2 words. Replace these with whatever is appropriate for your needs. The rest correctly grabs the third word regardless of whether it has 3, 4, 5, … words in the Name—assuming no oddities like double sapces, hard spaces, non-printing characters, etc.

Good luck.
This was selected as the best answer
Juhi Dhiver 5Juhi Dhiver 5
Thanks MellowRen Alott...

Could you please tell me how should I modify it. Like now i want to create for fourth word,So could you please tell me what logic should I apply here.
Waiting for your reply.

Thanks Once Again.
:)

 
MellowRenMellowRen
A “word four” will create a longer and messier formula. As requested, I will explain the logic.

First of all, this solution will produce bad results if there are odd spaces or charcters in Name. For the most part we can’t do much about this but we can get rid of extra spaces at the beginning and end of Name using the TRIM() function—hence why in all instances above I have TRIM(Name) rather than Name.

This chunk of formula finds the position of the second space in name: FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1 )

The logic here is that if there is a second space (remember the name is TRIM()ed ) there must be a third word. Think: word<space>word<space>word…  If there is less than three words, there will be no second space and this chunk will evaluate to 0 (zero)—hence the IF() statement that produces “<None>”.

If there is a third word (the IF() statement fails) then we start to extract it. Ignore the LEFT() for the moment, I’ll come back to it. First I grabbed everything after that 2nd space using the RIGHT() command. Similar to your formula I used the LEN(TRIM(Name)) the subtracted the position of the 2nd space to determine the number of chacters to grab—thus again using the 2nd space chunk written above.

This is all well and good but what we would have here might contain more than one word. This is why that result is encapsulated within that LEFT() statement. This is where it again gets quite repetitive. Let’s call the result of the extraction 3rdWordPlusText.

This is the formula logic: LEFT(3rdWordPlusText, IF( 3rdWordPlusText has no space, LEN(3rdWordPlusText), Up to the 1st space of 3rdWordPlusText))

That means the formula chunk for 3rdWordPlusText is used three times and the code to find a space, twice. Yuck. But it works.

To extract the fourth word, logic is the same but we start looking for the third space: FIND(" ", TRIM(Name), FIND(" ", TRIM(Name))+1, FIND(" ", TRIM(Name))+1)

And build from there :-)
Juhi Dhiver 5Juhi Dhiver 5
hey Mellow,

Thanks a lott....

I had append this logic ,but still not working co'z find can only have two paramaters not three.

Waiting for your reply.
Tell me what should I do...