Excel бағдарламасында мәтін мен сандарды қалай бөлуге болады

Кейде Excel бағдарламасында жұмыс істегенде, сіз мәтін мен сандар әдейі немесе байқаусызда байланыстырылған деректерді кездестіресіз. Мысалы, бірнеше цифрдың белгілі бір саны және оның алдындағы мәтін. Тапсырма осы санды мәтіннен ажырату. Немесе бұл әріптерді сандардан бөлу қажет болатын әріптік-цифрлық мақалалардың белгілі бір жиынтығы.

Сонымен бірге Excel бағдарламасында әдепкі бойынша жоспарланған нәрсені орындайтын функциялар немесе процедуралар жоқ. Болады ма? Жауап иә және төменде қадамдық нұсқаулық берілген.

Мәселені тікелей шешуге кіріспес бұрын, алдымен әр ұяшықтағы сандарды тауып, сол ұяшықтарда әдетте кириллица немесе латын әріптері бар екенін тексеру ұсынылады. Бұл деректерде көп жолдар болса, қажетті есептеу ресурстарын азайтады.

Бұл мақалада мен стандартты Excel функцияларын және үшінші тарап шешімдерін, соның ішінде Excel қондырмасын пайдалану арқылы мәтін мен сандарды бөлудің бар жолдарын қарастырамын.

Ескерту: бұл ұяшықтағы мәтін мен сандарды бөлу туралы. Бөлек ұяшықтағы сандарды таңдағыңыз келсе, осы мақаланы оқыңыз: Excel бағдарламасындағы ұяшықтан сандарды шығарып алыңыз.

Мәтіннен сандарды бөлу – Excel формулалары

Excel бағдарламасының әдеттегі мүмкіндіктерінен бастайық. Жолдағы мәтіндік және цифрлық кеңістіктердің саны анықталған және жолдан жолға өзгермейтін кезде олардың көмегімен есептерді шешу оңайырақ болады. Ең танымал опциялар:

• сандар алдындағы әріптер (x100, FX5200, MI6);
• әріптің/әріптердің алдындағы сан/сандар (2021, 3D, 4K, 4,2 Гц);
• сан-мәтін-саны (3k2, 22str3, 1EKX23).

Мәтіннің оң жағындағы санды бөліңіз

Ұяшық әріптерден басталып, сандармен аяқталатынын білсек, ұяшықтағы бірінші цифрдың орнын табуымыз керек. Мәліметтерді оқу үшін сілтемені орындаңыз, бірақ формуланың өзі:

{=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)}

Бұл массив формуласы! Төмендегі барлық ұқсас (бұйра жақшалармен) сияқты, ол енгізіледі:

1. бұйра жақшаларсыз;
2. CTRL + Shift + Enter (жай Enter орнына).

Әрі қарай, бұл техника мәселесі – осы позицияға фрагменттерді алу және одан бастап LEFT және MID функциялары бұған көмектеседі. Егер орын A2 ұяшығында, ал бастапқы мәтін A1 ұяшығында есептелсе делік, онда бірінші және екінші бөліктердің формулалары сәйкесінше болады:

=ЛЕВСИМВ(A1;A2-1)

=ПСТР(A1;A2;1000)

Бүкіл нәтижені бөлгіш арқылы бірден бір ұяшықта көрсеткіңіз келсе, жоғарыдағы үшеуден формула жасау керек:

{=ЛЕВСИМВ(A1;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)-1)
&" "&
ПСТР(A1;ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0);9999)}

Бұл формулада бос орын бөлгіші бар (жолдың ортасында), егер басқа бөлгішті пайдаланғыңыз келсе, оны өзіңіздікімен ауыстырыңыз.

Мәтіннің алдында сандарды бөліңіз

Ұяшықтың мәтіні сандардан басталып, мәтіндік таңбалармен жалғасатыны алдын ала белгілі болса, бірінші сандық емес символдың орнын алу керек. Мұны ұқсас массив формуласымен жасауға болады, жалғыз айырмашылығы: MATCH функциясы ЖАЛҒАН емес, TRUE мәнін іздейді:

{=ПОИСКПОЗ(ИСТИНА;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)}

Әрі қарай, процедура бірінші мысалға ұқсас.

Тұрақты өрнектер

Ұяшықтағы мәтін мен сандардың реті және фрагменттердің саны алдын ала белгісіз болған кезде тұрақты тіркестер үлкен көмек болады.

Өкінішке орай, қазіргі уақытта олар Excel бағдарламасында қораптан тыс жеткізілмейді, бірақ Google Sheets қолданбасында әдепкі бойынша қол жетімді.

Google парақтарында

Мәтін мен сандарды бөлетін Google Sheets формуласы өте қарапайым:

=TRIM(REGEXREPLACE(A1,"(\d+|\D+)","$1 "))

Тұрақты өрнектер және олардың қалай жұмыс істейтіні туралы толығырақ мына сайттағы менің мақаламнан оқи аласыз: Excel бағдарламасындағы тұрақты өрнектер (сілтеме).

Excel бағдарламасында !SEMTools қондырмасы бар

Дегенмен, тұрақты өрнек функциялары менің қондырманың барлық пайдаланушылары үшін ешқандай шектеусіз тегін қол жетімді – қондырманы Excel бағдарламасына қосу жеткілікті. Функциялар тек ол орнатылған компьютерлерде ғана дұрыс жұмыс істейтінін түсіну маңызды.

!SEMTools көмегімен мәтін мен сандарды екі рет басу арқылы бөліңіз

Уақытты бағалайтындар үшін мен мәтінді және сандарды ұяшық ішінде тікелей бөлетін немесе мұндай процедураның нәтижесін көрші бағанда көрсететін процедураны әзірледім – бұл нәтижелерді шығарудың жаһандық параметріне байланысты.

Сізге бар болғаны:

1. жай ғана ауқымды немесе ұяшықты таңдаңыз,
2. процедураны шақырыңыз
3. бөлгішті көрсетіңіз
4. және OK түймесін басыңыз.