এক্সেল গাইড: একাধিক ডিলিমিটার দিয়ে টেক্সট কলামে ভাগ করার পূর্ণ টিউটোরিয়াল

টেক্সট টু কলামস কনভার্ট করা আমাদের দৈনন্দিন কাজে খুব সাধারণ একটি টাস্ক। অসংখ্য পরিস্থিতিতে আমাদের টেক্সটকে কলামে ভাগ করতে হয়, আর এই কাজে মাইক্রোসফট এক্সেল অত্যন্ত দক্ষ। এই আর্টিকেলে আমরা এক্সেলে একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করার সব বاریকি আলোচনা করব।

এক্সেলে একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করার ৬টি পদ্ধতি

এই প্রসঙ্গে, নিচের বেস্ট সেলারদের তালিকা ডেটাসেটটি বিবেচনা করা হয়েছে যা B4:B13 সেলগুলিতে অবস্থিত, এতে বইয়ের নাম, লেখক, এবং ধরণ কলাম রয়েছে। আমরা বইয়ের নাম, লেখক, এবং ধরণ-কে আলাদা আলাদা কলামে ভাগ করতে চাই। আর विलম্ব না করে প্রতিটি পদ্ধতি আলাদা আলাদা এক্সপ্লোর করি।

এখানে মাইক্রোসফট এক্সেল ৩৬৫ ভার্সন ব্যবহার করা হয়েছে, আপনি সুবিধা অনুযায়ী অন্য ভার্সনও ব্যবহার করতে পারেন।

পদ্ধতি ১: টেক্সট টু কলামস ফিচার ব্যবহার

সর্বপ্রথম, এক্সেলের টেক্সট টু কলাম ফিচারের সাথে শুরু করব যা একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্টের জন্য একটি হ্যান্ডি টুল। তাই নিচের ধাপগুলো পর্যবেক্ষণ করে প্রক্রিয়াটি শিখি।

📌 ধাপগুলো:

• সবশেষে, B5:B13 সেলগুলো নির্বাচন করুন >> Data ট্যাবে যান >> Text to Columns অপশনে ক্লিক করুন।

তৎক্ষণাৎ, Convert Text to Columns উইজার্ডটি প্রদর্শিত হবে।

• তারপর Delimited অপশনটি বেছে নিন >> Next বাটন চাপুন।

• এই স্টেজে, Comma ডিলিমিটারের জন্য চেকмар্ক দিন >> Next বাটন চাপুন।

• পরিবর্তে, আপনার পছন্দমতো একটি Destination সেল দিন, এখানে এটি C5 সেল >> Finish বাটনে ক্লিক করুন।

• এখন একটি সতর্কতা দেখতে পাওয়া যেতে পারে, সেক্ষেত্রে OK বাটনে ক্লিক করুন।

পরে, চূড়ান্ত ফলাফল নিচের স্ক্রিনশটের মতো দেখাবে।

আরও পড়ুন: ক্যারিজ রিটার্ন সহ এক্সেলে টেক্সট টু কলামস ফিচার ব্যবহার

পদ্ধতি ২: TRIM, MID, SUBSTITUTE, REPT, এবং LEN ফাংশন সমুহ ব্যবহার

এক কথায়, ফাংশনগুলো এক্সেল স্প্রেডশীটের প্রাণ। এখানে আমরা TRIM, MID, SUBSTITUTE, REPT, এবং LEN ফাংশনগুলো কম্বাইন করব একাধিক ডিলিমিটার দিয়ে টেক্সট আলাদা করে কলামে রেখে। এখানে, LEN ফাংশন স্ট্রিংয়ের দৈর্ঘ্য রিটার্ন করে, আর REPT ফাংশন টেক্সট রিপিট করে। তারপর SUBSTITUTE ফাংশন পুরনো টেক্সটকে নতুন টেক্সট দিয়ে রিপ্লেস করে, MID ফাংশন টেক্সটের মধ্যের ক্যারেক্টারগুলো রিটার্ন করে। শেষে TRIM ফাংশন অতিরিক্ত স্পেসগুলো সরিয়ে দেয়।

📌 ধাপগুলো:

• প্রথমে C6 সেলে যান >> নিচের সমীকরণটি লিখুন।

=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))

এখানে, B6, এবং C5 সেলগুলো যথাক্রমে বইয়ের নাম, লেখক, ধরণ কলাম এবং সংখ্যা ১-কে বোঝায়।

ফর্মুলা ব্রেকডাউন:

• LEN($B6) → একটি টেক্সট স্ট্রিংয়ের ক্যারেক্টার সংখ্যা রিটার্ন করে। এখানে, B6 সেল হলো text আর্গুমেন্ট যা মান 43 দেয়।
  • আউটপুট → 43 ” “
• REPT(” “,LEN($B6)) → হয়ে যায়
  • REPT(” “,43) → একটি টেক্সট নির্দিষ্ট সংখ্যক বার রিপিট করে। এখানে, ” “ হলো text আর্গুমেন্ট যা ব্ল্যাঙ্ক স্পেস বোঝায়, আর 43 হলো number_times আর্গুমেন্ট যা ফাংশনকে নির্দেশ দেয় ৪৩ বার ব্ল্যাঙ্ক ইনসার্ট করতে।
  • আউটপুট → ” “
• SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) → একটি টেক্সট স্ট্রিংয়ে বিদ্যমান টেক্সটকে নতুন টেক্সট দিয়ে রিপ্লেস করে। এখানে, B6 বোঝায় text আর্গুমেন্ট, এরপর “,” উপস্থাপন করে old_text আর্গুমেন্ট, এবং REPT(” “,LEN($B6)) নির্দেশ করে new_text আর্গুমেন্ট যেটি কমা গুলোকে ব্ল্যাঙ্ক স্পেস দিয়ে রিপ্লেস করে।
  • আউটপুট → “Poor Economics Abhijit Banarjee Economics”
• MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)) → একটি টেক্সট স্ট্রিংয়ের মধ্য থেকে ক্যারেক্টারগুলো রিটার্ন করে, দেওয়া স্টার্ট পজিশন এবং লেন্থ অনুযায়ী। এখানে, SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) সেলটি হলো text আর্গুমেন্ট, (C$5-1)*LEN($B6)+1 হলো start_num আর্গুমেন্ট, এবং LEN($B6) হলো num_chars আর্গুমেন্ট যাতে ফাংশনটি বাম দিক থেকে প্রথম ক্যারেক্টার রিটার্ন করে।
  • আউটপুট → “Poor Economics “
• TRIM(MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6))) → হয়ে যায়
  • TRIM(“Poor Economics “) → একটি টেক্সট থেকে সিঙ্গল স্পেস ছাড়া সব স্পেস সরিয়ে দেয়। এখানে, “Poor Economics ” সেলটি হলো text আর্গুমেন্ট এবং ফাংশনটি টেক্সটের পরের অতিরিক্ত স্পেসগুলো সরিয়ে দেয়।
  • আউটপুট → “Poor Economics”

• দ্বিতীয়, Fill Handle Tool ব্যবহার করে ফর্মুলাটি সারিগুলোর জুড়ে কপি করুন।

• তৃতীয়, C6:E6 সেলগুলো নির্বাচন করুন >> Fill Handle টুল টেনে নিচের সেলগুলোতে ফর্মুলা প্রয়োগ করুন।

শেষে, আপনার আউটপুট নিচের ছবির মতো দেখাবে।

আরও পড়ুন: ফর্মুলা দিয়ে এক্সেলে টেক্সট টু কলামস অটোমেটিক্যালি স্প্লিট করা

পদ্ধতি ৩: LEFT, RIGHT, MID, LEN, এবং FIND ফাংশন কম্বাইন

বিকল্পভাবে, আমরা LEFT, RIGHT, MID, LEN, এবং FIND ফাংশনগুলোর কম্বো ব্যবহার করতে পারি একাধিক ডিলিমিটার দিয়ে টেক্সট আলাদা কলামে ভাগ করতে। এই কেসে, FIND ফাংশন দেয়া অ্যারেতে সংখ্যা খুঁজে বের করে, আর LEN ফাংশন টেক্সট স্ট্রিংয়ের দৈর্ঘ্য রিটার্ন করে। এরপর MID ফাংশন টেক্সট স্ট্রিংয়ের মধ্য থেকে ক্যারেক্টারগুলো রিটার্ন করে, আর LEFT এবং RIGHT ফাংশনগুলো স্ট্রিংয়ের বাম ও ডান প্রান্ত থেকে টেক্সট এক্সট্রাক্ট করে।
ধরে নিই আমাদের কাছে ক্লায়েন্টদের তালিকা ডেটাসেট আছে B4:B12 সেলগুলিতে, যেটিতে নাম, দেশ, এবং শহর কলাম রয়েছে যেগুলো সেমিকোলন দিয়ে আলাদা। এখানে আমরা নাম, দেশ, এবং শহর-কে আলাদা আলাদা কলামে ভাগ করতে চাই, তাই প্রক্রিয়াটি বিস্তারিত দেখি।

📌 ধাপগুলো:

প্রথমে, C5 সেলে নেভিগেট করুন >> Formula Bar-এ নিচের এক্সপ্রেশনটি ইনসার্ট করুন।

=LEFT(B5,FIND(";",B5)-1)

উপরের এক্সপ্রেশনে, B5 সেলটি উপস্থাপন করে নাম, দেশ, এবং শহর কলামগুলোকে।

ফর্মুলা ব্রেকডাউন:

• FIND(“;”,B5) → একটি টেক্সট স্ট্রিংয়ের ভেতর থেকে অন্য একটি টেক্সট স্ট্রিংয়ের স্টার্টিং পজিশন রিটার্ন করে। এখানে, “;” হলো find_text আর্গুমেন্ট আর B5 হলো within_text আর্গুমেন্ট। নির্দিষ্টভাবে, FIND ফাংশনটি স্ট্রিংয়েতে সেমিকোলন(;) ক্যারেক্টারের পজিশন রিটার্ন করে।
  • আউটপুট → 7
• LEFT(B5,FIND(“;”,B5)-1) → হয়ে যায়
  • LEFT(B5,7) → একটি স্ট্রিংয়ের শুরু থেকে নির্দিষ্ট সংখ্যক ক্যারেক্টার রিটার্ন করে। এখানে, B5 সেলটি হলো text আর্গুমেন্ট আর 7 হলো num_chars আর্গুমেন্ট যাতে ফাংশনটি বাম দিক থেকে ৭ ক্যারেক্টার রিটার্ন করে।
  • আউটপুট → Trevor

• পরে, D5 সেলে যান >> নিচের এক্সপ্রেশনটি টাইপ করুন।

=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)

ফর্মুলা ব্যাখ্যা:

• FIND(“@”,B5)-FIND(“;”,B5)-1 → এখানে, FIND ফাংশন স্ট্রিংয়েতে সেমিকোলন(;) এবং অ্যাট দ্য রেট (“@”) ক্যারেক্টারের পজিশন রিটার্ন করে।
  • 16 – 7 – 1 → 8
• FIND(“;”,B5)+1 → উদাহরণস্বরূপ, FIND ফাংশন স্ট্রিংয়েতে সেমিকোলন(;) ক্যারেক্টার লোকেট করে।
  • 7 + 1 → 8
• MID(B5,FIND(“;”,B5)+1,FIND(“@”,B5)-FIND(“;”,B5)-1) → হয়ে যায়
  • MID(B5,8,8) → এখানে, B5 সেলটি হলো text আর্গুমেন্ট, 8 হলো start_num আর্গুমেন্ট, এবং 8 হলো num_chars আর্গুমেন্ট যাতে ফাংশনটি প্রথম ৮ ক্যারেক্টার পরে ৮ ক্যারেক্টার রিটার্ন করে।
  • আউটপুট → Iceland

• পরে, E5 সেলে নিচের ফর্মুলাটি ইনসার্ট করুন।

=RIGHT(B5,LEN(B5)-FIND("@",B5))

এই ফর্মুলা কীভাবে কাজ করে:

• LEN(B5)-FIND(“@”,B5) → LEN ফাংশন B5 সেলের স্ট্রিংয়ের দৈর্ঘ্য রিটার্ন করে, অন্যদিকে FIND ফাংশন অ্যাট দ্য রেট (“@”) ক্যারেক্টারের পজিশন রিটার্ন করে।
  • 26 – 16 → 10

• RIGHT(B5,LEN(B5)-FIND(“@”,B5)) → হয়ে যায়
  • RIGHT(B5,10) → একটি স্ট্রিংয়ের শেষ থেকে নির্দিষ্ট সংখ্যক ক্যারেক্টার রিটার্ন করে। এখানে, B5 সেলটি হলো text আর্গুমেন্ট আর 10 হলো num_chars আর্গুমেন্ট যাতে ফাংশনটি ডান দিক থেকে ১০ ক্যারেক্টার রিটার্ন করে।
  • আউটপুট → Reykjavik

শেষে, ফলাফলগুলো নিচের স্ক্রিনশটের মতো দেখাবে।

পদ্ধতি ৪: ফ্ল্যাশ ফিল ব্যবহার

জটিল ফর্মুলা ব্যবহার করা আপনার মত না হলে, আমাদের পরের পদ্ধতিই আপনার খুঁজতে থাকা উত্তর হতে পারে। এখানে, আমরা এক্সেলের ফ্ল্যাশ ফিল ফিচার প্রয়োগ করব একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করতে।

📌 ধাপগুলো:

• সবশেষে, ম্যানুয়ালি নাম Trevor C5 সেলে লিখুন >> Home ট্যাবে, Fill ড্রপ-ডাউনে ক্লিক করুন >> Flash Fill অপশনটি নির্বাচন করুন।

এখন, এক্সেল বাকি সেলগুলো অটোফিল করবে।

এভাবেই দেশ এবং শহর কলামগুলোর জন্য প্রয়োগ করুন এবং চূড়ান্ত আউটপুট নিচের ইমেজের মতো দেখাবে।

পদ্ধতি ৫: পাওয়ার কোয়েরি ব্যবহার

স্বীকার করতে হবে, পাওয়ার কোয়েরি এক্সেলের একটি উপেক্ষিত ফিচার, যেটি ডেটা দ্রুত ও কার্যকরভাবে অর্গানাইজ ও অ্যানালাইজ করতে সক্ষম। নিচের সেকশনে, আমরা আলোচনা করব কীভাবে আমরা মাত্র چند ক্লিকেই একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করতে পারি। তাহলে, এটা একশনে দেখি।

📌 ধাপগুলো:

• প্রথমে, B4 সেলে যান >> কীবোর্ড শর্টকাট CTRL + T চাপুন এক্সেল টেবিল ইনসার্ট করতে >> OK চাপুন।

• পরবর্তী, Data ট্যাবে যান >> From Table/Range অপশনে ক্লিক করুন।

খুব দের না করে, Power Query Editor প্রদর্শিত হবে।

• এই পয়েন্ট থেকে, Split Column ড্রপ-ডাউন চাপুন >> By Delimiter অপশনটি বেছে নিন।

• তারপর, Semicolon অপশনটি সিলেক্ট করুন >> Each occurrence of the delimiter অপশনে চেক দিন >> OK বাটন চাপুন।

• এরপর, কলাম হেডারগুলোতে ডাবল-ক্লিক করে রিনেম করুন >> Close & Load অপশনে ক্লিক করুন Power Query উইন্ডো বন্ধ করতে।

শেষে, সব ধাপ কমপ্লিট করলে নিচের ফলাফল পাওয়া যাবে।

আরও পড়ুন: লাইন ব্রেককে ডিলিমিটার হিসেবে এক্সেল টেক্সট টু কলামসে ব্যবহার

পদ্ধতি ৬: VBA কোড প্রয়োগ

আপনি যদি প্রায়ই এক্সেলে একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করতে চান, তবে নিচের VBA কোড-টি বিবেচনা করতে পারেন। এটি সহজ ও সহজ, শুধু অনুসরণ করুন।

📌 ধাপগুলো:

• শুরুতে, Developer ট্যাবে নেভিগেট করুন >> Visual Basic বাটনে ক্লিক করুন।

क्षणে, Visual Basic Editor একটি নতুন উইন্ডোতে খুলবে।

• পরবর্তী, Insert ট্যাবে যান >> Module নির্বাচন করুন।

আপনার সুবিধার জন্য, এখান থেকে কোডটি কপি করে নিচের ছবির মত উইন্ডোতে পেস্ট করুন।

Sub Separate_Text_String() Dim Arr() As String, _ cnt As Long, _ j As Variant For k = 5 To 13 Arr = Split(Cells(k, 2), ";") cnt = 3 For Each j In Arr Cells(k, cnt) = j cnt = cnt + 1 Next j Next k End Sub

⚡ কোড ব্রেকডাউন:

এখানে আমি এক্সেলে একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করতে ব্যবহৃত VBA কোডটি ব্যাখ্যা করব। এই কেসে, কোডটি ২ ধাপে ভাগ করা হয়েছে।

• প্রথম অংশে, সাব-রুটিনকে একটি নাম দেওয়া হয়েছে, এখানে এটি হলো Separate_Text_String()।
• পরবর্তী, ভেরিয়েবল Arr, cnt, এবং j কে যথাক্রমে String, Long, এবং Variant হিসেবে ডিফাইন করা হয়েছে।

• দ্বিতীয় অংশে, প্রতিটি সেলের মাধ্যমে For Loop চালু করুন এবং সেমিকোলন দিয়ে ডিলিমিটেড টেক্সট স্প্লিট করুন।
• এখন, কোডে, স্টেটমেন্ট “For k = 5 To 13” ডেটার শুরুর এবং শেষ রো নম্বরকে বোঝায়, এখানে এটি ৫ থেকে ১৩।
• তারপর, “Arr = Split(Cells(k, 2), “;”)”-এ “;” হলো ডিলিমিটার যা আপনি কমা, পাইপ ইত্যাদিতে চেঞ্জ করতে পারেন।
• শেষে, “cnt = 3” বোঝায় তৃতীয় কলাম নম্বর (কলাম C)।

• এই পয়েন্টে, Run বাটন বা কীবোর্ডের F5 কী চাপুন।

চূড়ান্ত ফলাফল নিচের স্ক্রিনশটের মতো প্রদর্শিত হবে।

অনুশীলন বিভাগ

প্রতিটি শীটের ডান পাশে একটি Practice সেকশন দেওয়া আছে যাতে আপনি নিজে অনুশীলন করতে পারেন। দয়া করে নিজে করুন।

উপসংহার

সংক্ষেপে, এই আর্টিকেলে এক্সেলে একাধিক ডিলিমিটার দিয়ে টেক্সট টু কলামস কনভার্ট করার ৬টি কার্যকর পদ্ধতি দেখানো হয়েছে। পুরো আর্টিকেলটি মনোযোগপূর্বক পড়ুন এবং ফ্রি ওয়ার্কবুক ডাউনলোড করে অনুশীলন করুন। এখন, আশা করি আপনি এই আর্টিকেলটিকে সহায়ক পাবেন এবং আপনার যেকোনো الإضافی প্রশ্ন বা সুপারিশ থাকলে কমেন্টে জানাতে পারেন। অবশেষে, আরও অনেক এই ধরনের আর্টিকেলের জন্য ExcelDemy ভিজিট করুন।

সম্পর্কিত আর্টিকেলগুলো

• এক্সেলে তারিখের জন্য টেক্সট টু কলামস ব্যবহার (সহজ ধাপ সহ)
• [ফিক্সড!] এক্সেল টেক্সট টু কলামস ডেটা মুছে ফেলছে
• ওভাররাইটিং ছাড়া এক্সেলে টেক্সট টু কলামস কনভার্ট