Как да извлечете число от низ в Excel

Как да

отАдам Дейвидсън

Публикувано7 юни 2023 г

Excel е мощна електронна таблица, но не може да прави чудеса. Ако импортирате данни и тези данни са бъркотия, тогава Excel няма да може да направи много с тях. Например, ако имате стойности, които са прикачени към други знаци като част от низ, няма да можете да извършвате изчисления върху тях, освен ако първо не ги извлечете.

За щастие, възможно е да направите точно това, въпреки че може да е по-сложно, отколкото може да си помислите. Ето как да извлечете число от низ в Excel.

Как да извлечете число от края на низ с помощта на Flash Fill

Бърз и лесен начин да извлечете число от края на низ е да използвате Excel Flash Fill особеност. Тази функция ви позволява бързо да попълните диапазон от клетки въз основа на шаблона, открит в една конкретна клетка. Един недостатък на този метод е, че той изисква сами да направите първото извличане и ако искате да извлечете число само от един низ, това няма да е от полза.

В повечето случаи Flash Fill може правилно да извлече числото от края на низ, въпреки че не е безпогрешно, така че винаги трябва внимателно да проверявате резултатите. Ако не работи правилно, опитайте ръчно да попълните първите две или три клетки, а не само първата.

За да извлечете число от края на низ с помощта на Flash Fill:

1. Изберете клетката до първата клетка, съдържаща вашите низове.
   
2. Въведете числото, което се появява в края на низа.
   
3. Ако всичките ви данни са в непрекъсната колона без пропуски, изберете клетката точно под тази, в която току-що сте въвели числото.
   
4. Ако вашите данни имат пропуски в някои редове, изберете първата клетка и всички клетки под нея, от които искате да извлечете числа, включително всички празни редове.
   
5. Натиснете Ctrl+E на Windows или Cmd+E на Mac, за да стартирате функцията Flash Fill.
6. Останалите ви клетки автоматично ще бъдат попълнени въз основа на шаблона, който Excel е забелязал. В повечето случаи числата от края на низа ще бъдат извлечени правилно.
   

Как да извлечете число от края на низ с помощта на формула

Един недостатък на използването на метода Flash Fill е, че той изисква сами да извлечете числата от низа за първия ред. Възможно е да се извлече число от края на низ с помощта на формула, въпреки че формулата е доста сложна.

За да извлечете число от края на низ с помощта на формула:

1. Щракнете в клетката, където искате да се появи извлеченото число.
2. Въведете следната формула, като замените „B2“ с клетката, съдържаща вашия низ:

   =СТОЙНОСТ(ДЯСНО(B2,LEN(B2)-MAX(АКО(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT(" 1:"&LEN(B2))),0))))

3. Натиснете Въведете.
4. Числото трябва да бъде извлечено от низа.
5. За да приложите формулата към останалите клетки, щракнете върху манипулатора за плъзгане в долния десен ъгъл на клетката.
   
6. Плъзнете надолу върху клетките, където искате формулата да бъде репликирана.
   
7. Пуснете мишката и формулата ще бъде копирана в избраните клетки.
8. Сега тези клетки трябва да съдържат вашите извлечени числа.
   
9. Всички клетки без низ за извличане ще върнат грешка. Ако вместо това искате да върнете празна клетка, можете вместо това да използвате формулата:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Всички празни клетки сега ще върнат празно място.
    

Може би се чудите какво прави тази формула. Накратко, той генерира масив, който е със същата дължина като низа. Той поставя всеки от символите от низа в този масив по ред. След това оценява всеки от тези знаци, за да види дали са число или не. Всички знаци, които са числа, се преобразуват в нула. Всички знаци, които не са числа, се преобразуват в число, представляващо неговата позиция в низа.

Например AB5HG23 ще се преобразува в 1, 2, 0, 4, 5, 0, 0. След това търси най-високата стойност в този списък, която е позицията на последния знак, който не е число. Накрая връща всички знаци след тази точка, които са числата в края на низа. След това функцията VALUE преобразува това обратно в число. Просто, нали?

Как да извлечете число от началото на низ с помощта на Flash Fill

Можете също да използвате Flash Fill, за да извлечете число от началото на низ, като попълните сами първия пример и позволите на Excel да спре шаблона.

За да извлечете число от началото на низ с помощта на Flash Fill:

1. Изберете клетката вдясно от първия низ, който има число, което искате да извлечете.
2. Въведете числото, което се появява в началото на низа.
   
3. Ако всичките ви данни са в непрекъсната колона без пропуски, изберете клетката директно отдолу.
   
4. Ако вашите данни имат пропуски, изберете първата клетка и всички клетки под нея, от които искате да извлечете числа, включително всички празни редове.
   
5. Натиснете Ctrl+E на Windows или Cmd+E на Mac, за да стартирате Flash Fill.
6. Сега избраните от вас клетки трябва да показват извлечените числа.
   

Как да извлечете число от началото на низ с помощта на формула

Можете също да използвате формула, за да извлечете число от началото на низ. Това работи по начин, подобен на формулата в предишния раздел, но намира позицията на първия нечислов знак и връща всички знаци преди това.

За да извлечете число от началото на низ с помощта на формула:

1. Изберете клетката, в която искате да се появи извлеченото число.
2. Въведете следната формула, като замените „B18“ с клетката, съдържаща вашия низ:

   =СТОЙНОСТ(НАЛЯВО(B18,СЪОТВЕТСТВИЕ(НЕВЯРНО, ISNUMBER(MID(B18,РЕД(ИНДИРЕКТ("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Натиснете Въведете и числото ще бъде извлечено.
   
4. Можеш използвайте автоматично попълване в Excel за да копирате формулата във всички други клетки, от които се нуждаете.
5. Ако искате празните редове да върнат празен резултат, използвайте следната формула вместо това:

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

Как да извлечете числа от всяко място в низ с помощта на Flash Fill

Както споменахме по-рано, Flash Fill може да не работи, ако първата клетка не съвпада с формата на някои от другите клетки. В този случай може да се наложи да изчислите ръчно две или три клетки, преди Flash Fill да ви даде желания резултат.

За да извлечете числа от всяко място в низ с помощта на Flash Fill:

1. В клетката до първия низ въведете ръчно числата, които са в низа.
   
2. Ако всичките ви данни са в непрекъсната колона, изберете клетката директно отдолу.
   
3. Ако вашите данни имат пропуски, изберете първата клетка и всички клетки под нея, от които искате да извлечете числа, включително всички празни редове.
   
4. Натиснете Ctrl+E на Windows или Cmd+E на Mac, за да стартирате Flash Fill.
5. Сега клетките трябва да се запълнят с извлечените числа.
   

Как да извлечете числа от всяко място в низ с помощта на формула

Можете да използвате формула, за да извлечете всички числа от низ, независимо къде се появяват.

За да извлечете числа от всяко място в низ с помощта на формула:

1. Изберете клетката, в която искате да се появи извлеченото число.
2. Въведете следната формула, като замените „B9“ с клетката, съдържаща вашия низ:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Вашата формула трябва да извлече всички числа от низа.
   
4. Ако искате всички празни клетки да връщат празна клетка, а не нула, използвайте тази формула вместо това:

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Можеш използвайте автоматично попълване за да приложите формулата към всички други клетки във вашата електронна таблица.
6. Всички низове, които не съдържат числа, ще върнат нула. Можете да научите как да конвертирайте нула в тире в Excel ако искате да промените това.

Разширете уменията си за Excel

Научаването как да извличате число от низ в Excel може да ви помогне бързо да извлечете необходимите данни от списък с низове. Въпреки че изискваните формули може да са сложни, не е необходимо да разбирате как точно работят; точно това правят!

Има още полезни трикове на Excel, които можете да научите, за да изведете уменията си на следващото ниво. Можеш да научиш как да броим клетки с текст в тях, или как да изчислете нечия възраст от датата на раждане. Можете дори създайте генератор на произволни числа в Excel.