• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Text Formula

sachar

Member
Dear Troubleshooter,

With reference to the attachment in column “Q” separate the right word from the string sentence, Can you explore the formula with an example?
 

Attachments

  • Daily Experiences Activities & Excursion_Q.xlsx
    189.8 KB · Views: 0
You are welcome Sachar

So if you have data like this "*Belling,Birgitta,Mrs." You notice a pattern that you want all text after second comma .

1. FIND(",",B22) this will find you a position of first comma. with belling brigita its position 9

2. FIND(",",B22,FIND(",",B22)+1 - this will find a position of second comma because it looks for comma in cell B22, but it doesn't start from the beginning but from FIND(",",B22)+1 which is next character after first comma. So the second comma for above example is at position 18

3. LEN(B22) - gives you length of the whole string which is 22.

4. If you make difference 22-18 so formulas in 2 and 3 you get 4 which is the length of string you need . So in function right it will be used as parameter length

I hope it's clear now
 
Dear Sir,

With the help of yours, I understood the formula. Further, please help me, with the help of “MID” formula, how to complete the sentence like “Mrs. Birgitta”?
 
Hi I put the formula in cell U19
 

Attachments

  • Daily Experiences Activities & Excursion_Q.xlsx
    181 KB · Views: 0
In Row 1, paste following formula and copy down.

=TRIM(RIGHT(SUBSTITUTE(B1,",",REPT(" ",99)),99)&" "&LEFT(SUBSTITUTE(SUBSTITUTE(B1,",",REPT(" ",99)),"*",""),99))
 
Dear Shrivallabha,

Received with thanks, although, getting the correct answer, but, as a beginner, I am not clear about the logic, would you please explain?
 
Dear Shrivallabha,

Can you explore the formula how to work it?

=TRIM(RIGHT(SUBSTITUTE(B1,",",REPT(" ",99)),99)&" "&LEFT(SUBSTITUTE(SUBSTITUTE(B1,",",REPT(" ",99)),"*",""),99))
 
Back
Top