Microsoft Excel Me Text Formulas Ka Use Kaise Kare

Aaj ke iss excel tutorial me hum microsoft excel me formulas text function ke bare me janenge. Text option me kaun kaunsi commands hai. Aur kis tarah se aur kisliye unka use hota hai. Iss post me step by step janane ki koshish karenge. To chaliye start karte hai aur jante hai text formulas ke bare me.

Text formulas me 25 tarah ki commands hai jinka use aap excel spreadsheet me alag alag tarah se kar sakte hai. Jaisa ki aap neeche image me dekh sakte hai. Inn formulas ka kya use hai aur kaise text formulas ko excel worksheet me apply karte hai aaiye janate hai.

 

Microsoft Excel Me Text Formulas Ka Use Kaise Kare

Microsoft excel me formulas function me vaise to aur bhi sub tabs hai. But yahapar hum text formulas ke bare me janenge. To chaliye one by one karke janate hai formulas text ki commands ke bare me.

Find

Find ka use charactor ki position kaun se number pe hai ye show karne ke liye hota hai. For example word KANISHKA me N kaun se sthan par hai ye janane liye find command ka use karte hai.

Jaisa ki aap upar image me dekh sakte hai ki name KANISHKA me N 3rd place par hai. Issi tarah se aap kisi bhi shabd se koi perticular character ka place pata kar sakte hai.

Formula:- =Find(“Find_Charactor”,Cell_Add)

 

 

Search

Formula Search ka use bhi find ki tarah hi character ko search karne ke liye hota hai. For example name KANISHKA me H 6th place par hai, to ye janane ke liye search formula ka use kare.

Formula:- =Search(“Search_Charactor”,Cell_Add)

 

 

Len

Len formula ka use karke aap kisi word ya sentance me kitne characters hai ye pata kar sakte hai. For exampla name KANISHKA me 8 characters hai. To excel spreadsheet me len formula ka use karke iss name ke total characters ka count dekh sakte hai.

Formula:- =Len(Cell_Add)

 

 

Code

Microsoft excel me har alfa (A-Z) aur numeric (0-9) ka ek code hota hai. Vo bhi upper alfa aur lower alfa ka alag alag code hota hai. Code formula se aap inn characters (alpha/numeric) ka kya code hai ye pata kar sakte hai.

Formula:- =Code(Cell_Add)

 

 

Dollar

Iss formula ka use numbers me dollar sign add karne ke liye kiya jata hai. For example niche image me dekh sakte hai. 100 me $ ka sign add karna hai to iske liye dollar command ka use karenge.

Formula:- =Dollar(Cell_Add)

 

 

Char

Code formula ki tarah hi char formula ka bhi use hota hai. Jiss tarah se code formula ki help se character ke code show hote hai. Ussi prakar char formula ki help se aap ye pata kar sate hai ki kis number se kaun sa character show hoga.

Formula:- =Char(Cell_Add)

 

 

Right

Right formula use basically word ke right side me jo characters hai usko show karne karne ke liye hota hai. For example KANISHKA name ke right side se 1, 2 or 3 character extract karna hai. Jo ki A, KA or HKA hai to iske liye hum excel spreadsheet me right ka formula use karenge. Aap chahe to isse jyada characters ke liye bhi right formula use kar sakte hai.

Formula:- =Right(Cell_Add,Char_Number)

 

 

Left

Left formula ka use basically word ke left side me jo text hai usko extract karne karne ke liye hota hai. Jaise right formula ka use hota hai thik ussi prakar left formula bhi kaam karta hai. Fark sirf itna hota hai ki ye left side se characters ko extract karta hai. For example KANISHKA ek word hai aur hume iske left side se 1-3 (K, KA, KAN) character extract karna hai. Sirf ek character hi nahi aap chahe to left side sequence se kitne bhi characters extract kar sakte hai.

Formula:- =Left(Cell_Add,Char_Number)

 

 

Replace

Replace formula ka use character, word ya sentence ko replace (badalne) karne ke liye hota hai. Ya word ke beech me ek ya ek se jyada characters ko change karne ke liye hota hai. For example:-

  • RAMESH            Starting 2 characters RA Change with U
  • RAMESH            Starging 3 characters RAM Change with MAH
  • RAMESH            Middle Char M change with J
  • RAMESH            All characters change with KAMLESH

Formula:- =Replace(Cell_Add,Starting_Char_to_Change,Number_of_Char_to_Change_”Replace_with_Char”)

 

 

Lower

Lower formula se word ko lower case me kiya ja sakta hai. ya phir all sentance ko bhi aap lower case me kar sakte hai.

Formula:- =Lower(Cell_Add)

 

 

Proper

Proper formula se hum excel spreadsheed me kisi bhi word ya sentence ko upper lower case me conver kar sakte hai. Chahey sabhi words caps me ho ya lower me ho.

Formula:- =Proper(Cell_Add)

 

 

Trim

Excel worksheet ki cell me words or sentence ke starging or end ke space ko Trim formula se remove kiya ja sakta hai. For example name KANISHKA me K se pehle space hai aur last me A ke baad bhi space hai. Iss tarah ke kisi name ya sentence ke aage aur piche ke space ko delete karne ke liye trime formula ka use hota hai.

Formula:- =Trim(Cell_Add)

 

 

Upper

Upper formula se hum kisi bhi word ya sentence ko upper case yani ki caps me conver kar sakte hai. Chahe sabhi characters or sentence lower case me ho ya proper case me ho.

Formula:- =Upper(Cell_Add)

 

 

Concatenate

Concatenate formula ki help se do different cell ke words ko ek sath merge kar sakte hai. For example image me A1 cell me KANISHKA hai aur cell B1 me RAWAT hai. Ab inn names ko ek hi cell me show karna hai to iske liye concatenate formula ka use hota hai.

Formula:- =Concatenate(1st_Cell_Add,”space”,2nd_Cell_Add)

 

 

Rept

Rept formula help se hum ek ya multiple word or character ko kisi other cell me kai baar repeat kar sakte hai.

Formula:- =Rept(Cell_Repeat_value)

 

 

Exact

Exact formula help se hum ye pata kar sakte hai ki do different cell me same content hai ya nahi hai. Agar dono cells ke content same hai to result TRUE show karta hai. Agar same nahi hai to FALSE show karta hai. Diffrence kisi bhi tarah ka ho sakta hai. Upper, lower, proper ya character me difference etc.

Formula:- =Exact(1st_Cell_Add,2nd Cell_Add)

 

 

Substitute

Substitute formula ki help se aap kisi sentence ke beech me kisi perticular word ko change karne ke liye kar sakte hai. For example:-

  • Ram School Jata Hai
  • Ram College Jata Hai

Formula:- =Substitute(Cell_Add,”Old_Word”_”New_Word”)

 

 

Mid

Excel spreadsheed me aap mid formula ka use karke word or sentence ke beech se word/character ko extract kar sakte hai.

Formula:- =Mid(Cell_Add,Character_Num,Any_Alpha_Code)

 

 

Bahttext

Excel spreadsheet me Bahttext formula (0-9) numbers ko text me convert karta hai. Yani ki numerics ko thai language me convert karta hai.

Formula:- =Bahttext(Cell_Add)

 

 

T

Excel spreadsheet me T formula ki help se aap only numeric cell ko hide kar sakte hai. For example cell A1 me ek sentence hai jisme alpha aur numeric hai. Aur cell B1 me jo sentence hai usme only numeric hai. To iss formula ke use se only cell A1 show hoga aur cell B1 ka sentence show nahi hoga.

Formula:- =T(Cell_Add)

 

 

Clean

Excel spreadsheet me available non printable characters ko clean formula ki help se remove kar sakte hai.

Formula:- =Clean(Cell_Add)

 

Text

Excel spreadsheet me text formula ki help se aap numerics ko different formats me set kar sakte hai. Iss image me dekhkar aap khud samajh jayenge.

Formula:- =Text(Cell_Add,Diff_Criteria)

 

 

Value

Value function ki help se text (date formate, time formate) ko hum numerics me convert kar sakte hai. Microsoft excel me value function ki vaise koi khas jarurat nahi hai. Kyuki microsoft excel automatically text ko numeric values me convert karta hai.

Formula:- =Value(Cell_Add)

 

 

Fixed

Text formulas me available excel fixed function ki help se aap decimal values ko specified numbers me round kar sakte hai. Aur with or without comma/dot ke sath text me convert sakte hai.

Formula:- =Fixed(Cell_Add)

 

Text formulas ke last option insert function ki help se aap apne other formulas me functions insert kar sakte hai. Aur text formulas ke under other formulas ko modify bhi kar sakte hai. Iske bare me aapko next post me detail me bataya jayega.

 

Mujhe ummeed hai ki microsoft excel me text formulas ke bare me aap kafi kuchh jaan gaye honge. Text formulas se related agar aapka ko sujhav ho to comment jarur kare. Aur agar aapko text formulas se related ye jankari helpful lage to apne friends ke sath bhi share jarur kare.

Ramesh Singh

Hello Dosto ! Mera Naam Ramesh Singh Hai. Main Ek Passionate Blogger Hun. Ye Website Maine Banai Hai. Is Website Par Main Longo Ko Hindi Me Jankari Deta Hun Aur Unki Help Karta Hun. Read More

7 thoughts on “Microsoft Excel Me Text Formulas Ka Use Kaise Kare

Leave a Reply

Your email address will not be published. Required fields are marked *