• 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.

Need clarification on the excel formula.

J

Jagdev Singh

Active Member
Hi Experts

Could you please help me understand why are we using Rept funtion in the below formula.

This is the name "Belling,Birgitta,Mrs." I need "Mrs.".

I get the desire output with the formula - =TRIM(RIGHT(SUBSTITUTE(B22,",",REPT(" ",99)),99))

Please let me know what is Rept doing here.

Regards,
JD
 
Hi

The above result can also be fetched with using below code -

=TRIM(RIGHT(SUBSTITUTE(B22,",",REPT(" ",4)),4))

Why to use 99 if we can get the same result with 4.

Regards,
JD
 
Hi JD,

99 used in a standard way.
Let it understand....

I agree that you could use 4 but what could if string is Belling,Birgitta,ABCDE. & need to extract ABCDE.

Here 4 will only show CDE.

99 used as there would be len of last word is dynamic where you are free to manipulate it like as below.

=TRIM(RIGHT(SUBSTITUTE(B22,",",REPT(" ",LEN(B22))),LEN(B22)))
=TRIM(RIGHT(SUBSTITUTE(B22,",",REPT(" ",999)),999))

Let us know, If still any cloud of doubts around you.

Also check http://www.excel-easy.com/examples/formula-auditing.html
 
Hi Deepak

Excluding this below logic

"
Left name: =LEFT(A2,LEN(A2)-LEN(C2)-LEN(D2)-4)
Middle name: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-1)*99,99))
Right Name: =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))
"

Is there any way to find the left name from - Belling,Birgitta,Mrs

Regards,
JD
 
Fantastic sirji! So many ways to get the result. Thank you Deepak. This really gonna help me to improve further.

Regards,
JD
 
Hi Deepak

I am done with the homework! and encountered 2 doubts.

Array formula - =LEFT(A2,MATCH(",",MID(A2,ROW(1:99),1),0)-1) - to find the left word from the name.

Why are we using "Row(1:99)" in the above formula and what is its usage.

To find the middle name - =+MID(A2,FIND(",",A2)+1,FIND(",",REPLACE(A2,1,FIND(",",A2),""))-1)

What is the logic behind using "=+" in the beginning of the formula.

Also, I created one formula to find the left value

- =MID(A2,1,FIND("|",SUBSTITUTE(A2,",","|",1))-1)

Hope you will like it!

Regards,
JD
 
Good.
Let me clear the doubts!!!

First...
"Row(1:99)" used in array formula to create a number sequence of 1,2,3,4,5,6,7,8,9,10,11.....99.

Here same is used with MID function MID(A2,ROW(1:99),1). so it will create array of each letter of the input, like as below...

If the input is JAGDEV,SINGH then array will be like as "J","A","G","D","E","V",",","S","I","N","G","H" & then we find the position of the "," by match function & lastly left function will do his job.

Here ROW(1:99) used in a standard way! You could also use ROW(1:999) or ROW(1:50) or ROW(1:20) ......

Upper mark should be grater then the len of the input!!
Here len of the input JAGDEV,SINGH is 12 so that it should be at least ROW(1:12).
You would also use column(1:99) as well.

Long way of the same is ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))

Check this link for more explanation & other methods for the same.

http://forum.chandoo.org/threads/numeric-data-find-out-through-excel.22749/


I usually use "+" to start a formula & after hitting enter it auto get converted to "=+" . You are free to remove + or let it be.


=MID(A2,1,FIND("|",SUBSTITUTE(A2,",","|",1))-1)

Here, no need to use SUBSTITUTE function as we are finding the first position of ",".

Let me know if any further clouds around you.
 
You must log in or register to reply here.
Back
Top