CODEWITHSUNDEEP
excel
Shalini
Shalini

Reputation: 85

how to extract strings in excel in a specified format using string formula?

Tendulkar,Sachin Ramesh  
"=CONCATENATE(MID(C7,FIND(",",C7)+1,8)," ",LEFT(C7,FIND(",",C7)-1))"

For the above string, I want it as "Sachin R Tendulkar". I had written the above formula and it is working fine. Is there any other way to make dynamically Like instead of counting the characters and specifying it in find function?
Thanks in advance.

Upvotes: 0

Views: 197

Answers (1)

Daniel Lee
Daniel Lee

Reputation: 8031

So to find "Sachin", you can use: =mid(F17,find(",", F17,1)+1,len(F17)-find(" ",F17)) this finds the next number of letters after the comma until the space. To find R, you can use: =mid(F17,find(" ",F17)+1,1) this finds the first letter of the name after the space. Finally, you use =left(F17,find(",",F17)-1) to find the first name up until the comma. Then we can concatenate these three strings with spaces as follows:

=CONCATENATE(mid(F17,find(",", F17,1)+1,len(F17)-find(" ",F17))," ",MID(F17,find(" ",F17)+1,1)," ",left(F17,find(",",F17)-1))

enter image description here enter image description here

Upvotes: 2

Related Questions