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.

• Advance Excel Me Concatenate Formula Ka Use Kaise Kare
• Excel Me Duplicate Words Kaise Remove Kare
• Remove Numeric Macro Se Only Numbers Kaise Delete Kare
• Data Validation List Ka Excel Me Use Kaise Kare

Alphanumeric String Se Combined AlphaNumeric Characters Ko Kaise Alag Kare

Step 1

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

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.

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.

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.

• Hyperlink Image Macro Kaise Use Kare
• Hyperlink Text Macro Ka Use Kaise Kare
• Insert Chart Ke Bare Me Jane

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.

• Excel Formulas Me Symbol Kaise Add Karte Hai
• Data Advance Filter Kaise Use Kare
• Advance Excel Me Data Group Kya Hai

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.

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.

Advance Excel Tutorial Me Ye Bhi Padhe

• Data Goal Seek Ka Kya Use Hai
• H Lookup Aur V Lookup
• Text Formulas Ki Jaankari
• Advance Excel Me Home Tab Ki Puri Jaankari
• Data Sort Filter Ki Jaankari

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.