تقسیم متن از یک سلول و قرار دادن آن در چند سلول کاری است که تمام کاربران اکسل هر از چندگاهی با آن مواجه میشوند. امروز،تمرکز خود را بر روی بررسی عمیقتر نحوه تقسیم رشته ها با استفاده از ویژگی Split Text قرار میدهیم.
- نحوه تقسیم سلول ها در اکسل با استفاده از فرمول
- تقسیم رشته با استفاده از کاما، دو نقطه، ممیز، خط تیره و سایر جداکننده
- تقسیم رشته با استفاده از line break
- فرمول هایی برای تقسیم رشته به متن و عدد
- نحوه تفکیک سلول ها با استفاده از ویژگی split text
نحوه تقسیم متن در اکسل با استفاده از فرمول
برای تقسیم رشته در اکسل، می بایست از توابع right، left، mid در ترکیب با یکی از توابع find و search استفاده کرد. در نگاه اول ممکن است بعضی از فرمول ها پیچیده به نظر برسد ولی منطق این توابع بسیار ساده است و مثال های در زیر آمده درک روشنی برای شما ایجاد خواهد کرد.
تقسیم رشته به وسیله کاما، ممیز، خط تیره و دیگر جداکننده ها
زمان تقسیم سلول در اکسل، نکته کلیدی جایی است که جداکننده را در میان سلول قرار میدهید. بسته به نیاز شما این کار میتواند توسط تابع Serach (عدم حساس به حروف بزرگ و کوچک) یا find (حساس به حروف بزرگ و کوچک) انجام شود. زمانی که جای جداکننده را پیدا کردید با استفاده از توابع right، left، mid، قسمت مربوطه را از متن استخراج کنید. برای درک بهتر به مثال زیر توجه کنید:
فرض کنید لیستی از sku (واحد نگهداری موجودی) بر اساس الگوی آیتم-رنگ-اندازه در اختیار دارید و میخواهید این ستون را به سه ستون جداگانه تفکیک کنید.
- برای استخراج نام آیتم (تمام کاراکترها قبل از اولین خط تیره)، فرمول زیر را در سلول B2 قرار دهید، سپس آن را به سمت پایین در همان ستون کپی کنید.
=LEFT(A2, SEARCH("-",A2,1)-1)
در این فرمول، تابع serach محل اولین خط تیره را مشخص میکند و تابع left کاراکترهای سمت چپ آن را استخراج میکند (1 را از محل خط تیره کم میکنیم زیرا نمیخواهیم خط تیره هم برگردانده شود)
- برای استخراج رنگ (تمام کاراکترها بین خط تیره اول و خط تیره دوم) فرمول زیر را در سلول C2 وارد کنید، سپس آن را به سلول های پایین کپی کنید:
=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)
همانطور که احتمالاً میدانید ترکیب تابع mid به شکل زیر است:
MID(text, start_num, num_chars)
Text: رشته ای که میخواهید متن مورد نظر را از آن استخراج کنید.
start_num: محل اولین کاراکتری که میخواهید استخراج کنید.
num_chars: تعداد کاراکتری که میخواهید استخراج کنید.
در فرمول بالا، متن از سلول A2 استخراج میشود، و دو پارامتر بعدی توسط 4 تابع مختلف search محاسبه میشود:
start_num: محل اولین خط تیره به اضافه یک:
SEARCH("-",A2) + 1
num_chars: تفاوت محل دومین خط تیره با اولین خط تیره منهای 1:
SEARCH("-", A2, SEARCH("-",A2)+1) - SEARCH("-",A2) -1
برای استخراج اندازه(Size)، یعنی تمام کاراکترهای بعد از خط تیره سوم، فرمول زیر را در D2 قرار میدهیم :
=RIGHT(A2,LEN(A2) - SEARCH("-", A2, SEARCH("-", A2) + 1))
در این فرمول تابع len طول کل رشته را برمیگرداند که از آن محل دومین خط تیره را کم میکنیم. مقدار بدست آمده تعداد کاراکتر بعد از خط تیره دوم است و تابع right نیز آن را استخراج میکند.
به همین صورت، میتوانید ستونهایی را که متن درون آنها توسط دیگر جداکننده تفکیک شده است را جداسازی کنید . فقط کافیست به جای “-” در فرمول، ممیز، نقطه ویرگول فاصله یا هرچیز دیگری را قرار دهید.
نکته: در فرمول بالا، 1+ و 1- مربوط به تعداد کاراکتر جداکننده میشود. در این مثال خط تیره یک کاراکتر داشت. در صورتی که جداکننده شما بیش از یک کاراکتر داشته باشد برای مثال یک فاصله و یک کاما، فقط کاما “,” را درون تابع search قرار دهید و از 2+ و 2- به جای 1+ . 1- استفاده کنید.
نحوه تقسیم رشته با استفاده از line break (قرار گرفتن در سطر بعدی در سلول یا همان alt+enter)
برای تقسیم متن با استفاده از فاصله میتوانید از فرمول هایی که در بالا ارائه شد استفاده کنید. تنها تفاوت اینجاست که می بایست از تابع char برای قرار دادن کاراکتر line break استفاده کنید زیرا نمیتوانید مستقیماً آن را در فرمول وارد کنید.
فرض کنید سلول هایی که میخواهید تقسیم کنید شبیه به زیر باشد:
به جای خط تیره(-) در فرمول های بالا cahr(10) قرار دهید.10 کد ASCII برای تعویض خط است.
برای استخراج نام آیتم:
=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)
برای استخراج رنگ:
=MID(A2,SEARCH(CHAR(10),A2)+1,SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) - SEARCH(CHAR(10),A2) - 1)
برای استخراج اندازه:
=RIGHT(A2,LEN(A2) - SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))
و نتیجه شبیه به زیر خواهد شد:
نحوه تفکیک متن و عدد در اکسل
برای شروع باید گفت که هیچ روش کلی در مورد رشته های الفبا عددی وجود ندارد. اینکه از چه فرمولی استفاده شود بستگی به الگوی رشته مورد نظر دارد. در زیر فرمول هایی برای سه سناریو متفاوت ارائه شده است:
تفکیک رشته “متن+عدد”
فرض کنید، ستونی دارید که در آن متن و عدد با هم ترکیب شده است و همیشه یک عدد بعد از متن قرارگرفته است. شما میخواهید رشته اصلی را تفکیک کرده و عدد و متن را در سلول های متفاوتی قرار دهید؛ شبیه تصویر زیر:
نتیجه ممکن است به دو روش مختلف به دست آید.
روش 1: شمارش ارقام و استخراج آن کاراکترها
آسان ترین راه برای جدا کردن رشته متن وقتی که اعداد بعد از متن می آیند، این است:
برای استخراج عدد از فرمول آرایه ای زیر استفاده کنید و کلیدهای Ctrl + Shift + Enter را فراموش نکنید:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
برای استخراج متن از فرمول ساده زیر استفاده کنید:
=LEFT(A2,LEN(A2)-LEN(C2))
A2 رشته اصلی، C2 عدد اسخراج شده است که در تصویر زیر نیز نشان داده شده است:
روش 2: یک راه حل دیگر (فرمول غیر آرایه ای)
یک راه حل دیگر این است که از فرمول زیر برای مشخص کردن محل اولین عدد در رشته استفاده کنیم.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
اگرچه این فرمول نیز شامل یک ثابت آرایه ای است، ولی این فرمول مثل دیگر فرمول های معمولی با زدن کلید enter به درستی کار خواهد کرد.
زمانی که محل اولین عدد پیدا شد، میتوانید با استفاده از فرمول های ساده right و left به راحتی متن را از عدد تفکیک کنید.(به یاد داشته باشید که متن همیشه قبل از عدد می آید)
برای استخراج متن:
=LEFT(A2, B2-1)
برای استخراج عدد:
=RIGHT(B2, LEN(A1)-B2+1)
که A2 رشته اصلی و B2 محل اولین عدد است. تصویر زیر این فرمول را به صورت عملی نشان میدهد:
برای خلاص شدن از دست ستون کمکی که محل اولین عدد در آن قرار گرفته، میتوانید تابع min را در درون توابع right و left قرار دهید.
فرمول استخراج متن:
=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
فرمول استخراج عدد:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
مثال2. تفکیک رشته الگوی “عدد+متن”
اگر میخواهید رشته ای را تفکیک کنید که در آن متن بعد از عدد قرار گرفته، میتوانید اعداد را با استفاده از فرمول آرایه زیر بدست آورید:
=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))
این فرمول شبیه به فرمول آرایه ای مثال بالاست با این تفاوت که از تابع left به جای Right استفاده می کنید زیرا در اینجا اعداد در سمت راست رشته هستند.
زمانی که اعداد را بدست آوردید میتوانید با کم کردن تعداد ارقام از طول کل رشته متن را از رشته استخراج کنید.
=RIGHT(A2,LEN(A2)-LEN(B2))
در فرمول های بالا، A2 رشته اصلی و B2 عدد استخراج شده است که در تصویر زیر نیز نشان داده شده است:
از مطالعه این مطلب سپاسگذاریم . به امید دیدار
سلام و خسته نباشید
من یه داده ای دارم به شکل زیر که شامل متن فارسی و انگلیسی و عدد هست
MA020-1-داده خرید
این عبارت رو چطور میتونم جدا کنم؟چون با فرمول های بالا فقط عبارت انگلیسی چسبیده به عدد جدا میشه
سلام
فرمول های این مطلب ربطی به فارسی و انگلیسی بودن کاراکترخا ندارد.
بله ولی متن من ممکنه دو عبارت عددی داشته باشه
MA020810053-A- داده خرید آنتن/ MA020810053-A-
در این صورت باید فرمول search رو با & ترکیب کنم؟
در این صورت یکبار باید متن مورد نظر رو جدا کنید و بعد با تابع substitute متن قبلی رو حذف کنید و روی جمله جدید دوباره فرمول رو اجرا کنید.
ببخشید توی کامنت قبل دو عدد یکجور خورده،صحیحش اینه
MA020810054-A- داده خرید آنتن/ MA020810053-A-
سلام
من یک فایل اکسل دارم 1200 تا سلول با فرمول نویسی نوشته شدن.
چطوری میتونم سلول هارو به صورت متن کپی کنم؟؟؟؟؟؟
به صورت عادی کپی کنید، و موقع پیست کردن راست کلیک کنید و گزینه paste value رو انتخاب کنید.
سلام .من حسابدار رستوران هستم برای گرفتن مغایرت بانکی تمام گردش بانک رو در قالب اکسل گزارش میگیرم و با سورت کردن با اعداد حسابداریم که اون اعداد رو منتقل به اکسل میکنم از برنامه حسابداری میتونم مغایرت بگیرم .
مشکلم اینجاست که فرم اکسلی که بانک بهم میده از اینترنت بانک میگیرم سورت نمیشه با نمیتونم سام بگیرم ازش انگاری یه فرمولی داره که نمیشه اعداد رو سورت کرد یا سام گرفت چیکارش گنم به نظرتون که مشکلم حل شه .
ممنون میشم راهنمایی کنید .
سلام به این خاطر هست که خروجی که گرفتید در ظاهر عدد هست، ولی باطنن متن هست. اول باید مقدارها رو به عدد تبدیل کنید و بعد می تونید کارهایی که میخواید رو انجام بدید. اگر کنار سلول گوشه بالا یه علامت مثلث سبز هست، می تونید کل اعداد رو انتخاب کنید و روی علامت خطر زرد ررنگی که کنار سلول میاد کلیک کنید و گزینه convert to nubmer رو بزنید. اگر این مورد وجود نداشت باید ctrl+h بزنید و تک تک ارقام داخل سلول ها رو رو از 0 تا 9 رو با عدد جایگزین کنید. یعنی عدد 1… مطالعه بیشتر »
سلام
یه ستون شماره تلفن دارم می خوام با خط تیر از هم جدا بشن
مثلا 09180990666
تبدیل بشه به
0918-099-0666
سلام
با توابعی که توی همین مطلب هست متن رو بشکنید و بعد با & به هم بچسبونید.
همین کارو کردم موقع چسبوندن اینجوری شد!
0918-99-666
صفر رو لحاظ نمی کنه!!!
از کاراکتر اشتباهی متن رو جدا می کنید. عدد رو یکی کم کنید و طول متن رو یکی اضافه کنید.
سلام
لطف می کنید نحوه حذف اسلش (که اعداد را سه رقم سه رقم در جدول جدا کردم ) در ورد را توضیح دهید
با تشکر
سوالتون گنگ هست، ولی به صورت کلی می تونید با استفاده از ابزار find and replate (کلید ctrl رو نگه دارید و H رو بزنید.) هر چیزی رو به یه چیز دیگه جایگزین کنید. اگر بخواید حذف بشه، توی قسمت replace چیزی وارد نکنید.
سلام.
خیلی ممنون.فرمول خیلی جالبی بود.
من میخام تو ی جمله تمام کلمات جدا از هم رو مجزا بکنم. برای همین ” ” از فاصله استفاده کردم.اما جمله من مقید به 3 تا کلمه نیست.تعداد نامعلوم و زیادی کلمه داره.چیکار کنم همه کلمات رو برام مجزا بکنه.
سلام
چیزی که میخواید یک مقدار پیچیده هست. اگر بخواید راحت انجام بدید از گزینه text to column در تب data استفاده کنید. ولی اگر میخواید حتما با فرمول نویسی انجام بشه، باید این مطلب رو با مطلبی که توی لینک زیر میذارم ترکیب کنید و یک فرمول ترکیبی بنویسید:
سلام وقت بخیر …
تمام مراحل رو با دقت انجام دادم اما و به جای text از اعداد استفاده کردم اما فرمول اجرا نمیشه ممکنه راهنمایی کنید؟
سلام
باید خطایی که میده، مقدار یک سلول و فرمولی که نوشتید رو بفرستید تا مشخص بشه کجای فرمول رو اشتباه نوشتید.
متشکرم بزرگوار
سلام برای استخراج عدد بعد از =نمیدونم چیکار کنم-VAT INV-ARUP-P-L-19-06-0031(1)odcc=80,806,858 IRR
همین مطلب مطالعه کنید و مثالهاش حل کنید کامل توضیح داده شده. باید از ترکیب mid و search استفاده کنید.
سلام با تشکر از سعه صدر و پاسخگویی شما سوال دیگری دارم من دو ستون از اعداددارم، که در ستون اول آن اعداد تکراری وجود دار و ستون دوم هم مجموعه ای از اعداد است آیا روش یا کدی وجود داره که بتونم تعداد و مجموع اعداد ستون دوم که متناظر با یک عدد تکراری از ستون اول هست را محاسبه و در سلول جدید ذخیره کند و این کار را بصورت خودکار برای مابقی اعداد انجام و در سلول های مجزا ذخیره کند به عنوان مثال 2 1.2 2 2.5 2 3.4 1 4.2 1 5.2 9 12 9… مطالعه بیشتر »
از پیوت تیبل استفاده کنید.
ساخت پیوت تیبل در اکسل
ستون اعداد رو توی row قرار بدید.
ستون اعداد توی قسمت value هم قرار بدید و تابع محاسبه رو روی count بذارید.
تابع count در پیوت تیبل
ستون مقدارها رو توی value قرار بدید تا جمع اون ها رو هم حساب کنه.