এক্সেলে অরিজিনাল ডেটা ওভাররাইট ছাড়া টেক্সট কলামে বিভক্ত করুন

দিনেদিনের কাজে আমরা এক্সেলে টেক্সটকে কলামে রূপান্তর করতে পারি। মাইক্রোসফট এক্সেল এই কাজের জন্য একটি চমৎকার টুল। এবং অবশ্যই আপনি এই টপিকে পর্যাপ্ত সংখ্যক আর্টিকেল পাবেন। কিন্তু এই নিবন্ধের বিশেষত্ব হলো এখানে আমরা এক্সেলে ওভাররাইট ছাড়া টেক্সটকে কলামে রূপান্তর করার বিভিন্ন পদ্ধতি দেখাব। অর্থাৎ সোর্স কলামটি অক্ষত থাকবে ওভাররাইট হওয়ার বদলে। তাই কাজটি নিজে কার্যকরভাবে করতে এই নিবন্ধটি পড়ুন।

ভালো বোঝার এবং নিজে অনুশীলনের জন্য নিচের এক্সেল ওয়ার্কবুক ডাউনলোড করুন।

এক্সেলে ওভাররাইট ছাড়া টেক্সটকে কলামে রূপান্তর করার ৫টি পদ্ধতি

বোঝার সুবিধার্থে আমরা একটি প্রতিষ্ঠানের ছাত্রছাত্রীদের Name and Age List ব্যবহার করব। এই ডেটাসেটে Column B-এ Name, Age আছে।

এখন আমরা Column B-এর এই টেক্সটগুলো ওভাররাইট ছাড়া বিভিন্ন উপায়ে আলাদা কলামে রূপান্তর করব। এখানে ওভাররাইট মানে, কাজ করার সময় মূল টেক্সট অক্ষত থাকবে। তাহলে একে একে এক্সপ্লোর করি।
এখানে আমরা Microsoft Excel 365 ভার্সন ব্যবহার করেছি, আপনি সুবিধা অনুযায়ী অন্য ভার্সন ব্যবহার করতে পারেন।

১. LEN, MID, REPT, SUBSTITUTE এবং TRIM ফাংশন প্রয়োগ করা

যারা ফর্মুলার সাথে খেলতে পছন্দ করেন, এই পদ্ধতি তাদের জন্য। এই পদ্ধতিতে আমরা কিছু ফাংশন কম্বাইন করে আমাদের ফর্মুলা কাজ করাব। তাহলে চলুন, একশনে দেখি।

📌 Steps:

• শुरুর দাঁড়ি, C এবং D কলামের অধীনে Name এবং Age হেডিংসহ ২টি আলাদা কলাম তৈরি করুন।
• C5 এবং D5 সেলে যথাক্রমে ১ এবং ২ লিখুন। এটি এই নতুন কলামগুলোর নম্বর। আপনি আগামি ধাপগুলোতে এই ২ সেলের ফাংশন বুঝতে পারবেন।

• তারপর C6 সেলটি সিলেক্ট করুন এবং নিচের ফর্মুলা দাখিল করুন।

এখানে B6 এবং C5 সেল যথাক্রমে প্রথম Name, Age এবং নম্বর ১-কে রেফার করে।

• LEN($B6) → LEN ফাংশন একটি টেক্সট স্ট্রিং-এ কতগুলো ক্যারেক্টার আছে সেটি রিটার্ন করে। এখানে B6 সেলটি text আর্গুমেন্ট যা ১১ মান দেয়।
  • Output → 11
• REPT(" ",LEN($B6)) → হয়
  • REPT(" ",11) → REPT ফাংশন একটি টেক্সটকে দেওয়া সংখ্যা বার রিপিট করে। এখানে " " হলো text আর্গুমেন্ট যেটি ব্ল্যাঙ্ক স্পেস নির্দেশ করে, এবং ১১ হলো number_times আর্গুমেন্ট যা ফাংশনকে ১১ বার ব্ল্যাঙ্ক ইনসার্ট করতে নির্দেশ করে।
  • Output → “ ”
• SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) → SUBSTITUTE ফাংশন একটি টেক্সট স্ট্রিং-এ বিদ্যমান টেক্সটকে নতুন টেক্সট দিয়ে রিপ্লেস করে। এখানে B6 হলো text আর্গুমেন্ট, পরবর্তীতে “,” হলো old_text আর্গুমেন্ট, এবং REPT(" ",LEN($B6)) নির্দেশ করে new_text আর্গুমেন্ট যা কমাগুলোকে ব্ল্যাঙ্ক স্পেস দিয়ে রিপ্লেস করে।
  • Output → John 18 yr
• MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)) → MID ফাংশন একটি টেক্সট স্ট্রিং-র মাঝ থেকে ক্যারেক্টারগুলো রিটার্ন করে দেওয়া স্টার্টিং পজিশন এবং দৈর্ঘ্য অনুযায়ী। এখানে SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) সেলটি text আর্গুমেন্ট, (C$5-1)*LEN($B6)+1 হলো start_num আর্গুমেন্ট, এবং LEN($B6) হলো num_chars আর্গুমেন্ট যেন ফাংশন বাম পাশ থেকে প্রথম ক্যারেক্টার রিটার্ন করে।
  • Output → John
• TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6))) → হয়
  • TRIM(John ) → TRIM ফাংশন একটি টেক্সট থেকে একচেটিয়া স্পেস ছাড়া সব স্পেস সরিয়ে দেয়। এখানে John সেলটি text আর্গুমেন্ট, এবং ফাংশন টেক্সটের পরের অতিরিক্ত স্পেসগুলো সরিয়ে দেয়।
  • Output → John

• এরপর ENTER চাপুন।

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

তাই আমরা টেক্সটের ডান পাশের অংশটি, যেটি হলো বয়স, D6 সেলে দেখতে পাচ্ছি।

• এই সময় দুটি সেল C6 এবং D6 একসাথে সিলেক্ট করুন। কার্সরকে D6 সেলের বটম-রাইট কর্নারে নিয়ে যান। আপনি আবার Fill Handle টুলটি দেখতে পাবেন।
• তারপর সেটিতে ডাবল-ক্লিক করুন নিচের সেলগুলোর ফর্মুলা প্রয়োগ করতে।

আমাদের সামনে ফলাফলটি আসছে।

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

২. LEFT, LEN, RIGHT এবং SEARCH ফাংশন ইনসার্ট করা

জটিল এক্সপ্রেশন লিখতে এবং ফাংশন কম্বাইন করতে চিন্তিত? তাহলে আমাদের দ্বিতীয় পদ্ধতিই আপনার প্রার্থনার উত্তর! এই পদ্ধতিতে আমরা ফর্মুলা ব্যবহার করব, কিন্তু এই বার এটিকে বুঝতে সহজ হবে। তাহলে আর দেরি না করে দেখি কিভাবে আমরা এটি করি।

📌 Steps:

• প্রথমে C5 সেলে যান এবং নিচের ফর্মুলা ইনসার্ট করুন।

এখানে B5 প্রথম Name, Age-কে সার্ভ করে।

• SEARCH(“,”,B5) → SEARCH ফাংশন একটি টেক্সট স্ট্রিং-এ অন্য একটি টেক্সট স্ট্রিং-এর শুরুয় позиции রিটার্ন করে। এখানে “,” হলো find_text আর্গুমেন্ট, B5 হলো within_text আর্গুমেন্ট। সপষ্টভাবে, SEARCH ফাংশন স্ট্রিং-এ কোমা(,) ক্যারেক্টারের পজিশন রিটার্ন করে।
  • Output → 5

• LEFT(B5,SEARCH(“;”,B5)-1) → হয়
  • LEFT(B5,5) → LEFT ফাংশন একটি স্ট্রিং-র শুরু থেকে নির্দিষ্ট সংখ্যক ক্যারেক্টার রিটার্ন করে। এখানে B5 সেলটি text আর্গুমেন্ট, ৫ হলো num_chars আর্গুমেন্ট যেন ফাংশন বাম পাশ থেকে ৫ ক্যারেক্টার রিটার্ন করে।
  • Output → John

• দ্বিতীয়ে, ENTER কী চাপুন।

এখন আমরা D5 সেলে ফলাফল পেতে অনুরূপ একটি ফর্মুলা ব্যবহার করব। পূর্বের ফর্মুলা-তে আমরা টেক্সট স্ট্রিং-র প্রথম অংশ পেতে LEFT ফাংশন ব্যবহার করেছি। এখানে আমরা স্ট্রিং-র দ্বিতীয় অংশ আনব।

• প্রস্তুতে, D5 সেলে যান এবং নিচের ফর্মুলা ইনসার্ট করুন।

• LEN(B5)-SEARCH(“,”,B5) → LEN ফাংশন B5 সেলের স্ট্রিং দৈর্ঘ্য রিটার্ন করে, বিপরীতে SEARCH ফাংশন কোমা (,) ক্যারেক্টারের পজিশন রিটার্ন করে।
  • Output → 11 – 5 → 6
• RIGHT(B5,LEN(B5)-SEARCH(“,”,B5)) → হয়
  • RIGHT(B5,6) → RIGHT ফাংশন একটি স্ট্রিং-র শেষ থেকে নির্দিষ্ট সংখ্যক ক্যারেক্টার রিটার্ন করে। এখানে B5 সেলটি টেক্সট আর্গুমেন্ট, ৬ হলো num_chars আর্গুমেন্ট যেন ফাংশন ডান পাশ থেকে ৬ ক্যারেক্টার রিটার্ন করে।
• Output → 18 yr

• এটি অনুসরণ করে ENTER চাপুন।

৩. Text to Columns ফিচার ব্যবহার করা

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

📌 Steps:

• প্রথমে B5:B14 রেঞ্জের সেলগুলো সিলেক্ট করুন।
• তারপর Data ট্যাবে যান।
• এরপর Data Tools ড্রপ-ডাউন আইকনে ক্লিক করুন।
• ড্রপ-ডাউন লিস্ট থেকে Text to Columns ফিচারটি সিলেক্ট করুন।

क्षणে এটি Convert Text to Columns ডায়ালগ বক্স খুলবে।

• স্টেপ ১-এ, Choose the file type that best describes your data সেকশনের অধীনে Delimited সিলেক্ট করুন।
• তারপর Next বাটনে ক্লিক করুন।

• স্টেপ ২-এ, Delimiter সেকশনে Comma সিলেক্ট করুন।
• এরপর Next ক্লিক করুন।

• তৃতীয় এবং চূড়ান্ত স্টেপে, Destination সেল হিসেবে C5 সেট করুন।
• শেষে Finish বাটনে ক্লিক করুন।

ধাপগুলো সম্পন্ন হলে এক্সেল একটি MsgBox দেখাবে সতর্কবার্তা সহ। চিন্তা করবেন না।

• সেখানে শুধু OK ক্লিক করুন।

অদ্ভুত, এক্সেলে ওভাররাইট ছাড়া টেক্সট টু কলাম কনভারশন সম্পন্ন।

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

৪. Flash Fill ফিচার ইমপ্লিমেন্ট করা

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

📌 Steps:

• প্রাথমিকভাবে John লিখুন C5 সেলে। এটি এই সেলে কাঙ্ক্ষিত ফলাফল। কিন্তু প্রথমবার আমাদের ম্যানুয়ালি করতে হয়।
• তারপর Home ট্যাবে যান।
• এরপর Editing গ্রুপে Fill ড্রপ-ডাউন আইকনে ক্লিক করুন।
• এটি অনুসরণ করে Flash Fill ফিচারটি সিলেক্ট করুন।

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

Similarly, Column D-এর অধীনে Age কলামের জন্যও একই কাজ করুন।

আরও পড়ুন: এক্সেল Text to Columns-এ Line Break ডিলিমিটার হিসেবে ব্যবহার

৫. VBA কোড প্রয়োগ করা

যদিও ফর্মুলা ব্যবহার ডেটা এডিট করার একটি দ্রুত উপায়, কিন্তু এটি ব্যাখ্যা করা কঠিন হতে পারে। আরো, যদি আপনি সেটি অটোমেটিক্যালি করার প্রয়োজন পড়ে 자주, তবে নিচের VBA কোড বিবেচনা করতে পারেন।

📌 Steps:

• প্রাথমিকভাবে, Developer ট্যাবে যান।
• দ্বিতীয়ে, Code গ্রুপে Visual Basic সিলেক্ট করুন।
• বিকল্পভাবে, কীবোর্ড শর্টকাট ALT + F11 ব্যবহার করুন একই কাজ করার জন্য।

হঠাৎ Microsoft Visual Basic for Applications উইন্ডো আসবে।

• এখানে প্রজেক্ট এক্সপ্লোরার সেকশনে Sheet6(VBA) এ ডাবল-ক্লিক করুন।

क्षणে ডান পাশে একটি কোড মডিউল খুলবে।

• এই উদাহরণে, নিচের কোড কপি করে মডিউলে পেস্ট করুন।

Sub Text_to_Columns_without_Overwriting() Dim Arr() As String, cnt As Long, j As Variant For k = 5 To 14 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 কোড ব্যাখ্যা করব যা ওভাররাইট ছাড়া টেক্সটকে কলামে রূপান্তর করে। এই কেসে কোডটি ২ ধাপে ভাগ করা হয়েছে।

• প্রথম অংশে সাব-রুটিনকে একটি নাম দেয়া হয়েছে, এখানে সেটি Text_to_Columns_without_Overwriting()।
• পরবর্তী, ভেরিয়েবল Arr, cnt, এবং j কে যথাক্রমে String, Long, এবং Variant হিসেবে ডিফাইন করা হয়েছে।
• দ্বিতীয় অংশে, প্রতিটি সেলের মাধ্যমে For Loop ব্যবহার করে টেক্সটকে কমা ডিলিমিটেড করে বিভক্ত করা হয়েছে।
• এখন, কোডে For k = 5 To 14 স্টেটমেন্টটি ডেটার শুরুর এবং শেষ সারি নম্বর নির্দেশ করে, এখানে ৫ থেকে ১৪।
• তারপর Arr = Split(Cells(k, 2), “;”)-এ “,” হলো ডিলিমিটার যা আপনি ইচ্ছা করলে সেমিকোলন, পাইপ ইত্যাদিতে পরিবর্তন করতে পারেন।
• শেষে, cnt = 3 তৃতীয় কলাম নম্বর (Column C) নির্দেশ করে।

• এখন রিবনে সবুজ রঙের প্লে বাটনে ক্লিক করুন। আসলে এটি Run বাটন। আপনি কীবোর্ডে F5 চাপেও একই কাজ করতে পারেন।

চূড়ান্ত ফলাফল নিচের স্ক্রিনশটে দেওয়া আছে।

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

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

উপসংহার

এই নিবন্ধে এক্সেলে ওভাররাইট ছাড়া টেক্সটকে কলামে রূপান্তর করার পদ্ধতি সহজ এবং সংক্ষিপ্তভাবে ব্যাখ্যা করা হয়েছে। Practice ফাইলটি ডাউনলোড করা না ভুলুন। এই নিবন্ধ পড়ার জন্য ধন্যবাদ। আশা করি এটি সাহায্যকারী হয়েছে। কমেন্ট সেকশনে আপনার যেকোনো প্রশ্ন বা পরামর্শ জানাতে পারেন। আরও এক্সেল সমাধানের জন্য আমাদের ওয়েবসাইট, Exceldemy ভিজিট করুন।

সম্পর্কিত নিবন্ধ

• এক্সেলে ডেটের জন্য Text to Columns ব্যবহার (সহজ ধাপে)
• [Fixed!] Excel Text to Columns Is Deleting Data
• এক্সেলে একাধিক ডিলিমিটার সহ Text to Columns রূপান্তর