এই টিউটোরিয়ালে দেখানো হবে কিভাবে এক্সেলে ফর্মুলার সাহায্যে সেলকে ডিলিমিটার দিয়ে ভাগ করতে হয়। ডিলিমিটার হল একটি ক্যারেক্টার যা টেক্সট স্ট্রিংয়ের ভেতরে ডেটার ভিন্ন অংশগুলিকে আলাদা করে। এই আর্টিকেলে আমরা এক্সেলে ফর্মুলা ব্যবহার করে সেলকে ডিলিমিটার দিয়ে ভাগ করার বিভিন্ন পদ্ধতি দেখাব।
প্র্যাকটিস ওয়ার্কবুক
নিচের লিংক থেকে ওয়ার্কবুকটি ডাউনলোড করতে পারেন।
ফর্মুলা ব্যবহার করে সেলকে ডিলিমিটার দিয়ে ভাগ করার ৮টি ভিন্ন পদ্ধতি
এক্সেলে সেলগুলো ভাগ করার প্রয়োজন হতে পারে বিভিন্ন পরিস্থিতিতে। এটি ঘটতে পারে যখন কেউ ইন্টারনেট, ডাটাবেস বা সহকর্মী থেকে তথ্য কপি করে। পুরো নাম থাকলে সেগুলিকে প্রথম এবং শেষ নাম হিসেবে আলাদা করা এক্সেলে সেল ভাগ করার সহজ উদাহরণ।
১. এক্সেল স্ট্রিং ফাংশন এবং SEARCH ফাংশন কম্বাইন করে ড্যাশ/হাইফেন ডিলিমিটার দিয়ে টেক্সট ভাগ করুন
ডিলিমিটার দিয়ে ভাগ করার জন্য প্রথমে ডিলিমিটারটি খুঁজে বের করতে হবে। একবার ডিলিমিটার খুঁজে পেলে আপনি সহজে ডিলিমিটারের যেকোনো পাশ থেকে মান বের করতে পারেন। আমরা SEARCH ফাংশন ব্যবহার করে ডিলিমিটার লোকেট করব, তারপর LEFT, MID বা RIGHT ফাংশন দিয়ে মান বের করব।
১.১ LEFT এবং SEARCH ফাংশন ইন্টিগ্রেট করুন
শুরু করি। যেহেতু LEFT ফাংশনের দুটি প্যারামিটার থাকে—টেক্সট এবং ক্যারেক্টার সংখ্যা। টেক্সট আমরা জানি। ক্যারেক্টার সংখ্যায় SEARCH ফাংশন ব্যবহার করব।
ধাপগুলো:
- প্রথমে সেলটি সিলেক্ট করে ফর্মুলা লিখুন।
=LEFT(B5, SEARCH("-",B5,1)-1)- এন্টার কী চাপুন।
- ফিল হ্যান্ডেল আইকন নিচে টেনে ফর্মুলা রেঞ্জে কপি করুন। অথবা প্লাস (+) চিহ্নে ডাবল-ক্লিক করে অটোফিল করুন।
- শেষে ফলাফল দেখতে পাবেন।
🔎 ফর্মুলা কীভাবে কাজ করে?
উদাহরণে আমাদের ডিলিমিটার হাইফেন ‘–’। SEARCH ফাংশন হাইফেনের পজিশন দেবে। আমাদের হাইফেনটিই নয়, হাইফেনের আগের অংশটি चाहिये।
১.২ MID এবং SEARCH ফাংশন মার্জ করুন
এখন মাঝপথের মান বের করতে MID এবং SEARCH ফাংশন ব্যবহার করব। নিচের ধাপগুলো অনুসরণ করুন।
ধাপগুলো:
- সেল সিলেক্ট করে নিচের ফর্মুলা লিখুন।
=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)- এন্টার চাপুন।
- ফিল হ্যান্ডেল টেনে বা প্লাস চিহ্নে ডাবল-ক্লিক করে রেঞ্জে কপি করুন।
- এখন সব মাঝপথের মান আলাদা হয়েছে।
🔎 ফর্মুলা কীভাবে কাজ করে?
SEARCH ফাংশন একটি টেক্সট স্ট্রিংয়ের ভেতরে অন্য টেক্সট স্ট্রিংয়ের লোকেশন রিটার্ন করে। এটি হাইফেনের পরের ক্যারেক্টার থেকে শুরু করে। দেওয়া ক্যারেক্টার সংখ্যা অনুযায়ী MID নির্দিষ্ট സ്ഥান থেকে টেক্সট বের করে।
১.৩ RIGHT, LEN এবং SEARCH ফাংশন কম্পাউন্ড করুন
সবচেয়ে শেষ মান আলাদা করতে RIGHT, LEN এবং SEARCH ফাংশনের কম্বিনেশন ব্যবহার করব। নিচের ধাপগুলো দেখুন।
ধাপগুলো:
- সেল সিলেক্ট করে ফর্মুলা লিখুন।
=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1))- এন্টার চাপুন।
- ফিল হ্যান্ডেল টেনে বা প্লাস চিহ্নে ডাবল-ক্লিক করে ফর্মুলা কপি করুন।
- এভাবে ডিলিমিটার দিয়ে শেষ মান ভাগ হয়ে যাবে।
🔎 ফর্মুলা কীভাবে কাজ করে?
এখানে LEN ফাংশন স্ট্রিংয়ার মোট দৈর্ঘ্য রিটার্ন করে, 거기서 শেষ হাইফেনের পজিশন বিয়োগ করা হয়। SEARCH ফাংশন হাইফেনের পজিশন দেয়। পার্থক্যটি হাইফেনের পরের ক্যারেক্টার সংখ্যা, যা RIGHT ফাংশন বের করে।
নোট: আপনি অন্য যেকোনো ক্যারেক্টারের মাধ্যমে সকলম কলাম ভাগ করতে পারেন। শুধু ‘– ’ বদলে আপনার ডিলিমিটার দিন।আরও পড়ুন: কমা দিয়ে স্ট্রিং ভাগ করার এক্সেল ফর্মুলা (৫ উদাহরণ)
২. লাইন ব্রেক দিয়ে টেক্সট ভাগ করার জন্য ফর্মুলা মার্জ করুন
লাইন ব্রেক দিয়ে স্ট্রিং ভাগ করতে আগের সেকশনের মতো ফর্মুলা ব্যবহার করতে হবে। এক্সট্রা একটা ফাংশন লাগবে—CHAR।
২.১ LEFT, SEARCH এবং CHAR ফাংশন কম্বাইন করুন
CHAR ফাংশন লাইন ব্রেক ক্যারেক্টার দেবে। প্রথম মান বের করতে LEFT, SEARCH এবং CHAR ফাংশন ব্যবহার করব। ধাপগুলো নিচে দেওয়া হলো।
ধাপগুলো:
- আগের মেথডের মতো সেল সিলেক্ট করে শীর্ষ মান বের করার ফর্মুলা লিখুন।
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)- এন্টার চাপুন ফলাফল দেখতে।
- প্লাস চিহ্ন টেনে রেঞ্জের বাকি সেলগুলোতে ফর্মুলা কপি করুন।
🔎 ফর্মুলা কীভাবে কাজ করে?
10 লাইনের ASCII কোড। CHAR-এ ১০ দিয়ে লাইন ব্রেক সার্চ করা হয়। একটি নম্বর দ্বারা নির্ধারিত ক্যারেক্টার রিটার্ন করে। তারপর ব্রেকটি খুঁজে শীর্ষ মান রিটার্ন করে।
২.২ MID, SEARCH এবং CHAR ফাংশন একসাথে যোগ করুন
মাঝপথের মান আলাদা করার ধাপগুলো নিচে।
ধাপগুলো:
- অন্যান্য পদ্ধতির মতো সেল সিলেক্ট করে ফর্মুলা লিখুন।
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)- এন্টার চাপুন।
- প্লাস চিহ্ন টেনে রেঞ্জের ফলাফল পান।
২.৩ RIGHT, LEN, CHAR এবং SEARCH ফাংশন জয়েন করুন
টেক্সটের ডান পাশের জন্য RIGHT, LEN, CHAR এবং SEARCH ফাংশনের কম্বিনেশন ব্যবহার করব। নিচের ধাপগুলো অনুসরণ করুন।
ধাপগুলো:
- পূর্বের টেকনিকের মতো সেল সিলেক্ট করে নিচের ফর্মুলা লিখুন।
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))- কীবোর্ড থেকে এন্টার চাপুন।
- প্লাস চিহ্ন টেনে রেঞ্জের ফলাফল পান।
আরও পড়ুন: এক্সেলে সেল ভাগ করার ৫ সহজ ট্রিক
৩. এক্সেলে টেক্সট এবং নাম্বার স্ট্রিং প্যাটার্ন দিয়ে সেল ভাগ করুন
এই সেকশনে দেখব কিভাবে ক্যারেক্টার স্ট্রিং এর পর নম্বর থাকা টেক্সট ভাগ করতে হয়। সহজতর করতে আমরা শিটে কিছু পরিবর্তন আনলাম (চিন্তা করবেন না, সব শিট ওয়ার্কবুকে থাকবে)। উদাহরণে ছাত্রের নাম এবং আইডি একসাথে এক কলামে আছে, সেগুলো দুটি আলাদা কলামে ভাগ করব।
৩.১ RIGHT, SUM, LEN এবং SUBSTITUTE ফাংশন কম্বাইন করুন
SUBSTITUTE-এ নম্বরগুলো স্পেস দিয়ে রিপ্লেস করে LEN দিয়ে কাউন্ট করা হয়। টেক্সটের পর নম্বর থাকা ফরম্যাটের স্ট্রিং ভাগ করতে আগে নম্বরটি বের করতে হয়, তারপর সেই নম্বরের সাহায্যে টেক্সট বের করা যায়।
ধাপগুলো:
- শুরুতে ফলাফল দেখাতে চাইলে সেল সিলেক্ট করুন। আমাদের কেসে C5 সেল।
- সেলে ফর্মুলা লিখুন।
=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে রেঞ্জের ফলাফল পান।
🔎 ফর্মুলা কীভাবে কাজ করে?
নম্বর বের করতে হলে আমাদের স্ট্রিংয়ের ভেতরে ০ থেকে ৯ পর্যন্ত প্রতিটি সম্ভাব্য নম্বর খুঁজে বের করতে হয়। তারপর নম্বরগুলোর টোটাল পেয়ে স্ট্রিংয়ার শেষ থেকে 그만 ক্যারেক্টার রিটার্ন করা হয়।
৩.২ LEFT এবং LEN ফাংশন ইন্টিগ্রেট করুন
টেক্সট মান বের করতে এখন LEFT ফাংশন এবং ক্যারেক্টার সংখ্যায় সেলের মোট দৈর্ঘ্য থেকে ডিজিটের দৈর্ঘ্য বিয়োগ করব। ডিজিটগুলো আমরা D5 সেল থেকে পেলাম, কারণ আগের মেথডে আইডি ভাগ করেছিলাম।
ধাপগুলো:
- শুরুতে নির্দিষ্ট সেল সিলেক্ট করে ফর্মুলা লিখুন।
=LEFT(B5,LEN(B5)-LEN(D5))- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে গ্রুপের সেলগুলোতে ফলাফল পান।
আরও পড়ুন: এক্সেল VBA: ক্যারেক্টার সংখ্যা দিয়ে স্ট্রিং ভাগ করা (২ সহজ পদ্ধতি)
৪. নম্বর এবং টেক্সট স্ট্রিং প্যাটার্ন দিয়ে সেল ভাগ করুন
যদি আপনি ‘টেক্সট + নম্বর‘ ভাগ করার মেথড বুঝতে পারেন, তবে আশা করি নম্বর + টেক্সট ফরম্যাটের স্ট্রিং ভাগ করার পদ্ধতিও কল্পনা করতে পারছেন। পদ্ধতিটি আগের মতোই, শুধু একটা পরিবর্তন লক্ষ্য করবেন। এখন নম্বর টেক্সটের বামপাশে আছে, তাই নম্বর বের করতে LEFT ফাংশন এবং ক্যারেক্টার টেক্সটের জন্য RIGHT ফাংশন ব্যবহার করব।
৪.১ LEFT, SUM, LEN এবং SUBSTITUTE ফাংশন মার্জ করুন
শীর্ষ মান বের করতে LEFT, SUM, LEN এবং SUBSTITUTE ফাংশন মার্জ করতে হবে।
ধাপগুলো:
- প্রথমে নির্দিষ্ট সেল সিলেক্ট করে ফর্মুলা লিখুন।
=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে গ্রুপের সেলগুলোতে ফলাফল পান।
৪.২ RIGHT এবং LEN ফাংশন কম্পাউন্ড করুন
শেষ মান বের করতে RIGHT এবং LEN ফাংশন কম্বাইন করতে হবে।
ধাপগুলো:
- নির্দিষ্ট সেল সিলেক্ট করে ফর্মুলা লিখুন।
=RIGHT(B5,LEN(B5)-LEN(C5))- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে সেটের সেলগুলোতে ফলাফল পান।
৫. RIGHT, LEN, FIND এবং SUBSTITUTE ফাংশন কম্বাইন করে ডেটা সেল থেকে ভাগ করুন
আপনার টেক্সট থেকে ডেটা ভাগ করতে RIGHT, LEN, FIND এবং SUBSTITUTE ফাংশনের কম্বিনেশন ব্যবহার করতে পারেন।
ধাপগুলো:
- ইচ্ছিত সেল সিলেক্ট করে ফর্মুলা লিখুন।
=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে সেটের সেলগুলোতে ফলাফল পান।
🔎 ফর্মুলা কীভাবে কাজ করে?
যেহেতু ডেটা মান স্ট্রিংয়ার শেষে থাকে, তাই মাস, তারিখ এবং বছর বের করার জন্য নির্দিষ্ট সংখ্যক ইনস্ট্যান্স ট্রাভার্স করা হয়েছে। আপনার টার্গেট মান আরও টেক্সট দিয়ে চলে থাকলে ইনস্ট্যান্স সংখ্যা বদলে সেগুলো বের করতে পারেন।
নোট: এই ফর্মুলা তখনই কাজ করবে যখন আপনার টেক্সট স্ট্রিংয়ার শেষে ডেটা থাকে।আরও পড়ুন: এক্সেল VBA: স্ট্রিং সেলে ভাগ করা (৪ উপযোগী অ্যাপ্লিকেশন)
৬. FILTERXML এবং SUBSTITUTE ফাংশন কম্বাইন করে সেল ভাগ করুন
দেওয়া xpath ব্যবহার করে FILTERXML ফাংশন XML ডকুমেন্ট থেকে নির্দিষ্ট ডেটা বের করে। আমরা FILTERXML এবং SUBSTITUTE ফাংশন কম্বাইন করে সেল ভাগ করতে পারি। চলুন ফর্মুলা দিয়ে ডিলিমিটার দিয়ে সেল ভাগ করি।
ধাপগুলো:
- প্রথমে ইচ্ছিত সেল সিলেক্ট করে ফর্মুলা লিখুন।
=FILTERXML(""&SUBSTITUTE(B5,",","")&"- এন্টার চাপুন ফলাফল দেখতে।
- অ্যডিশন চিহ্ন টেনে কলেকশনের সেলগুলোতে ফলাফল পান।
🔎 ফর্মুলা কীভাবে কাজ করে?
এখানে SUBSTITUTE টেক্সট স্ট্রিংয়ের নির্দিষ্ট টেক্সট রিপ্লেস করে। তারপর এক্সেলের FILTERXML ফাংশন XML ফাইল থেকে ডেটা বের করতে সক্ষম করে।
৭. TEXTSPLIT ফাংশন অ্যাপ্লাই করুন ডিলিমিটার দিয়ে সেল ভাগ করতে
কলাম এবং রো ডিলিমিটার হিসেবে ব্যবহার করে টেক্সট সিকোয়েন্স ভাগ করতে TEXTSPLIT ফাংশন ব্যবহার করা হয়। আপনি সেটিকে রো বা কলাম অনুযায়ী ভাগ করতে পারেন। এটি ডিলিমিটার দিয়ে যেকোনো সেল ভাগ করার সবচেয়ে ছোট এবং সহজ পদ্ধতি। ধাপগুলো নিচে।
ধাপগুলো:
- ফলাফল দেখাতে চাইলে সেল সিলেক্ট করে ফর্মুলা লিখুন।
=TEXTSPLIT(B5,",")- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে সেটের সেলগুলোতে ফলাফল পান।
৮. TRIM, MID, SUBSTITUTE, REPT এবং LEN ফাংশন কম্বাইন করে সেল ভাগ করুন
অন্য একটি ফর্মুলা কম্বিনেশন হলো TRIM, MID, SUBSTITUTE, REPT এবং LEN ফাংশন, যা এক্সেলে ফর্মুলা দিয়ে ডিলিমিটার দিয়ে সেল ভাগ করতে সাহায্য করে।
ধাপগুলো:
- সেল সিলেক্ট করে ফলাফল দেখাতে চাইলে ফর্মুলা লিখুন।
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))- এন্টার চাপুন।
- অ্যডিশন চিহ্ন টেনে গ্রুপের সেলগুলোতে ফলাফল পান।
🔎 ফর্মুলা কীভাবে কাজ করে?
এখানে LEN ক্যারেক্টার সংখ্যা রিটার্ন করে। তারপর SUBSTITUTE নির্দিষ্ট স্থানের টেক্সট রিপ্লেস করে। MID ফাংশন নির্দিষ্ট স্থান থেকে টেক্সট বের করে। শেষে TRIM ফাংশন টেক্সট থেকে সাদা স্পেস বাদ দেয় (ডাবল স্পেস বাদে)।
এক্সেলে কমান্ড ব্যবহার করে ডিলিমিটার দিয়ে সেল ভাগ করুন
এক্সেলে সেল ভাগ করার একটি বিল্ট-ইন ফিচার রয়েছে। এটি Data Tab-এ পাবেন। ফিচারটি ব্যবহার করতে নিচের নির্দেশনা মেনে চলুন।
ধাপগুলো:
- প্রথমে সেল বা কলাম সিলেক্ট করুন (বেশির ভাগ সময় পুরো কলাম সিলেক্ট করতে হয়)।
- তারপর Data tab-এ যান। Data Tools সেকশনে Text to Columns অপশন পাবেন।
- সেটিতে ক্লিক করুন।
- একটি ডায়লগ বক্স আসবে। সাধারণত ডিলিমিটার দিয়ে ভাগ করতে হয়, তাই Delimited অপশন চেক করে Next চাপুন।
- তবে кільখনি ডিলিমিটার দেখতে পাবেন।
- আপনার পছন্দের ডিলিমিটার সিলেক্ট করুন বা নিজের ডিলিমিটার ইনপুট দিন।
- ডিলিমিটার সিলেক্ট করলে বক্সের নিচে প্রিভিউ দেখাবে।
- Next চাপুন।
- এই উদাহরণে কমা সিলেক্ট করা হয়েছে, কারণ মানগুলো কমা দিয়ে সেপারেটেড।
- Next চাপলে ভ্যালু টাইপ সিলেক্ট করার অপশন আসবে, Finish চাপলে আলাদা মান পাবেন।
- ফিলহাল General (ডিফল্ট) রেখেছি। নিচের ছবিতে কিছু ফরম্যাটিং করার পর ফলাফল দেখানো হলো।
উপসংহার
উপরোক্ত পদ্ধতিগুলো এক্সেলে ফর্মুলা দিয়ে ডিলিমিটার দিয়ে সেল ভাগ করতে সাহায্য করবে। আজকের জন্য এতটুকু। উপরের মেথডগুলো ব্যবহার করে আপনি ডিলিমিটার দিয়ে সেল ভাগ করতে পারবেন। আমরা ফর্মুলা দিয়ে সেল ভাগ করার பல পদ্ধতি লিস্ট করার চেষ্টা করেছি। আশা করি এটি উপকারী হবে। কঠিন লাগলে কমেন্ট করুন। আমরা মিস করলে অন্য পদ্ধতিও জানাতে পারেন।
আরও পড়ুন
- এক্সেলে একটি সেলকে দুইটি করে ভাগ করার ৫ উপযোগী পদ্ধতি
- এক্সেলে একক সেলকে আড়াআড়ি ও অনুভূমিকভাবে অর্ধেক ভাগ করা
- VBA দিয়ে স্ট্রিং এক্সেলে একাধিক কলামে ভাগ করা (২ পদ্ধতি)
- এক্সেল VBA: ক্যারেক্টার দিয়ে স্ট্রিং ভাগ করা (৬ উপযোগী উদাহরণ)
- এক্সেলে এক সেলে দুটি লাইন করার ৪ পদ্ধতি
