ADVANCE EXCEL

Alphanumeric String Kya Hai Advance Excel Me In Hindi

Hello dosto, aaj ki post me hum advance excel me ek sath jude (combined) alpha aur numbers ko kaise alag karte hai, iske bare me janenge. Alphanumeric string ki help se hum ek cell me jude huye akshar aur ank ko separate kar sakte hai.

Excel worksheet ki cell me combined numbers aur alpha ko aap manually bhi alag kar sakte hai. But agar aapke sheet me ek lambi list ho, to usse manual alag karna kaafi mushkil kaam hoga. Isliye aaj me aapko aisi excel tricks ke bare me batane jaa raha hu, jisse aap aasani se apni puri list ko seconds me split kar sakte hai. Iss usefull methods se aap apna kaam jaldi complete kar sakte hai, to chaliye start karte hai.

Alphanumeric String Kya Hai Advance Excel

Advance excel me alphanumeric string ek VBA command hai. Jiske through kisi ek cell me merge alpha aur numeric characters ko split kar sakte hai. Yani ki excel ki cell me ek sath jude huye akshar aur numbers dono ko alag cell me show kar sakte hai.

Forexample niche image me dekh sakte hai. First column me ek list hai, jisme alpha ke sath numeric bhi juda hua (combined) hai. Ab iss list se numbers ko ya alpha ko excel ki agli cell me alag karna hai.

Alphanumeric String

 

Alphanumeric String Se Combined AlphaNumeric Characters Ko Kaise Alag Kare

Step 1

Sabse pehle combined alpha aur numeric list ki excel worksheet open kare.

Alphanumeric String

  1. View tab par click kare.
  2. Macros me click kare.
  3. View macros par click kare.

 

 

Step 2

Ab ek popup box open hoga.

Alphanumeric String

  1. Macro name me koi bhi name enter kijiye.
  2. Create button par click kare.

 

Step 3

Ab ek module sheet open hogi, isme pehle se kuch words honge, unko delete kar de. Iske baad niche diya code paste karna hai. Vaise aap sidhe Alt+F11 se bhi ye module sheet open kar sakte hai.

Alphanumeric String

Ye Code Paste Kare

FunctionRetNum(Str AsString)
'updateby Extendoffice 20151214
    DimxRegEx AsObject
    SetxRegEx = CreateObject("vbscript.regexp")
    xRegEx.Global = True
    xRegEx.Pattern = "[^\d]+"
    RetNum = xRegEx.Replace(Str, "")
    SetxRegEx = Nothing
EndFunction
FunctionRetNonNum(Str AsString)
    DimxRegEx AsObject
    SetxRegEx = CreateObject("vbscript.regexp")
    xRegEx.Global = True
    xRegEx.Pattern = "[\d]+"
    RetNonNum = xRegEx.Replace(Str, "")
    SetxRegEx = Nothing
EndFunction

 

Step 4

Code copy paste karne ke baad apni worksheet me wapas aaye. Aur ab simple formulas se alpha aur numeric me se kisi ko bhi alag column me show kar sakte hai.

 

Numeric Different Column Me Select Kare

Numbers ko alag column me show karne ke liye  “=RetNum(cell address)” ye formula ko list ke agli cell me use karna hai. Niche image me dekh sakte hai.

Alphanumeric String

 

Alpha Different Column Me Alag Karne Ke Liye

Sirf alpha ko agle column me show dikhana hai to “=RetNonNum(cell address)” iss formula ko use kare. Niche image dekhe.

Alphanumeric String

 

Issi tarike se aap apni excel worksheet me akshar (alpha) aur ank (numbers) ko alag kar sakte hai. Upar diye dono formulas ka result niche image me dekhe.

Alphanumeric String

Alphanumeric String

 

Advance Excel Tutorial Me Ye Bhi Padhe

 

I hope, ab aap samajh gaye honge, ki advance excel me alphanumeric characters ko kaise dusre column me alag karte hai. Agar phir bhi aapka koi saval ho to apne comment ke through humse puchh sakte hai. Hume reply karne me khushi hogi.

Aur agar excel me alphanumeric string ki ye jaankari aapko helpful lagi ho to isse social networking sites par apne dosto ke sath share bhi 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

8 thoughts on “Alphanumeric String Kya Hai Advance Excel Me In Hindi

  • sir ji mere liye ek ms excel main problam hai uska solusion kar dijiye sir ji main ms excel main koi kaam karta hu to ms excel main Account dalne ka kaam sabse jyada hota hai aur usme kuch account ese bhi hote hai ki bo jyada bade hote hai unmain ek problam hoti hai unke last bale number change autometic change ho jate hai aur us number ki jagah par 0 ho jata hai kya karu please solusion

    Reply
    • Iske liye exl sheet ko select all kijiye aur right click karke format cell par click kijiye number option me text select kar ok kar dijiye. Hope aapka kaam ho jayega

      Reply
      • sir ji nahi ho raha hai iska topic main bata raha hu
        435557668724 sbin30080 31441100479
        628714476266 bkid9065 906510110004835
        260665499607 bkid9065 906510110004790
        616392741712 bkid9065 906510110007059
        958518878875 bkid9065 906510510000455
        630022090804 bkid9065 906510510000427
        207257586074 sbin30080 31494749262
        620463699748 punb985300 9853001700013205′
        ye account no. hai aur inme sabse last main jo number hai usmain coma ya ‘ nisan lagana padta hai ab to solusion dijiye please sir ji

        Reply
        • mujhe lagta hai aap other excel sheet se paste kar rahe hai, agar aisa hai to jaisa maine pehle bataya tha aap pehle vaise kijiye aur baad me direct paste karne ki bajay copied account numbers ki value paste kare, iske liye paste karne se pehle right click karna hai aur paste special me jakar text select karte huye ok button press kar dena hai, i hope iska solution ho jayega.

          Reply
  • Sir muje excel me ek problem aa rhi h me koi sheet bnata hu wo niche tak bahut badi line me h me uski fist raw ko hamesha upar dekhna chahta hu jese a1 h wo upar show ho rhi ho me kam a65 pr kru phir b a1 upar front pr show kese krege

    Reply
    • iske liye aap apni excel sheet me sabse pehle B1 cell par click kare aur upar data tab me jaye iske baad sub tabs me freee panes par click kar pehle wala option freeze panes par click kar dijiye, hope aapka kaam ho jayega.

      Reply
  • upendra choudhary

    Combin formula samajh me nhi arha hai sar ji bata skate hai example 8,3 ka sirf bata dijia

    Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.