• 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

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”?
 
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))
 
You must log in or register to reply here.
Back
Top