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

Separate names from text

soumyajit

New Member
Amt. paid to Swapna Das for bb. comp. against a/f. no- A07635737 doc- 30.07.07


676766 Chq. issued in favour of Dulal Debnath for bb. comp. against a/f. no- A07273936 doc- 31.07.07


sir,this time i just wanted to know how to separate names from any sentence or texts using one & only formula.As you can see above i have 2nos sentence or texts.I tried a same formula to separate "Swapna Das"(which is a Bengali name) from the first sentence and "Dulal Debnath"(which is also Bengali name)from the second sentence.But i failed to do this.Now i relay want your help.Plz help me out.I also here thinking you for yesterdays help.
 
Hi soumyajit,


I do not see any specific pattern in your data that we can consider to extract the names (Swapna Das or Dulal Debnath).


Do you have the list of these specific names saved somewhere or these are randomly coming in each statement?


Can you post some more examples of such statements so that we can try to understand if there is any specific factor that we can pick up.


Looking forward to your reply.


Kaushik
 
Hi Soumyajit..


I wish, Excel Bengali version may have option to search only Bengali Names..


for the time being can you please try the below formula..


Code:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),(SEARCH("for",SUBSTITUTE(A2," ",REPT(" ",99)))-296),197))


where provided Sentence with Name is in A2..


Regards,

Deb
 
ooh.. all BENGALI's are in full form...


"Dekha jaak.. kaaj kare ki na.."


Regards,

Deb


PS: For the community : "Lets see.. if it works.."
 
Excellent Debraj....you are a real champ dude...


Can you plz explain the mechanism behind?


Sotti oshadharon...


PS: For the community :Really outstanding..


Waiting for your reply..


Kaushik
 
May I ask you one question Debraj?


If the statement doesn't have "for" after the name in every cases, then how do we tackle that situation?


Sorry if I asked something wrong...


Kaushik
 
Hi Kaushik,


I found a pattern that, all names are just before FOR and with 2 WORD, so I search for FOR and pick up just two SPACEs behind it..

But to search for a single space and to search behind is really a tough thing.. so I added a huge range(99) of SPACE by =REPT(" ",99)


Now I have sentence, where each space changes to 99 space by =Substitute(...," ",99 Space)

So whatever the WORD (obviously less than 99 character) it will in between 1,99 or 100,198 or 197 to 296 .. and so on..


So, Now I have to search for FOR and to go behind 300(actually 296) character's and then come forward 200 character(actually 197).. by =MID(...,-296,197)
..


and by the way.. unlike LTRIM & RTRIM,

TRIM function removes all the Space, not only from LEFT and RIGHT but also double space if in MIDDLE..


Regards,

Deb


PS: This formula only works if name was BENGALI and a FOR after it.. lol..

PPS: If no FOR, then I will download all the names, and then I will search from the INDEX page..

http://www.babynology.com/search-babyname.php?gender=m&stw=D&origin=bengali&Submit=Search

Yeppy...In the URL, first name was mine...
 
Hi Koushik!


Thanks for your those kind words.. These kind words are the only reward's which motivates us to help you better..


BTW, OP has still not confirmed.. ;)


Regards,

Deb
 
Back
Top