MS Excel

Dd, prosím o radu, jak by se dalo vyseparovat číslo z buňky, která je textová např. KOLO457DSBA - tudíž vyseparovat číslo 457, ale pokaždé by bylo na jiné pozici, tzn. nějaké textové funkce typu část, vlevo, vpravo zde nefungují a hodnota čísla bude vždy jiná...

Třeba je to blbost, ale už nad tím celkem dlouho přemýšlím, a zjevně blbě :-(

Předem díky za radu,

Jirka32

Zaslat odpověď >

Milan-D(29.10.2012 15:55)#010098

Vzorcem: mozna jo, pokud bude zaruceno mnoho zjednodusujicich podminek (napr. vzdy tam bude trojcisli, nebudou se tam vyskytovat mezi cisly carky, lomitka, mezery.....
Makrem - lze, viz http://wall.cz/index.php?m=topic&id=9933#post-9952citovat

Dingo(29.10.2012 16:35)#010102

Co si napsat vlastní funkci, je to sice jako makro, ale zase člověk aspoň vidí vzoreček v buňce

.
Function najdi_cislo(retez As String) As Integer
' najde prvni souvislou radu cisel v retezci
Dim delka As Integer, i As Integer
Dim cifry As String, znak_x As String
delka = Len(retez)
cifry = ""
For i = 1 To delka
znak_x = Mid(retez, i, 1)
If IsNumeric(znak_x) Then
cifry = cifry + znak_x
Else ' neni cislo
If Len(cifry) > 0 Then ' uz ne znak zkraje retezce
Exit For
End If
End If
Next i
najdi_cislo = Val(cifry)
End Function edit: zapsáno samozřejmě do modulu ve vba (Alt+F11)
Ukázka i v příloze, neošetřené např. pro číselnou buňku

Příloha: zip

10102_najdi_cislo.zip (12kB, staženo 90x)

citovat

Poki(29.10.2012 16:50)#010104

...a nebo pouzit jen funkce na listu (tedy zadna makra) a akceptovat to, ze cisla museji byt za sebou (tedy: KOLO456blabla) - je jedno, na jakem miste a kolik jich bude, ale nesmi byt mezi cisly pismena

=ČÁST($B$2;SMALL(KDYŽ(JE.ČISLO(HODNOTA(ČÁST($B$2;ŘÁDEK(NEPŘÍMÝ.ODKAZ("E1:E" &DÉLKA(B2)));1)));ŘÁDEK(NEPŘÍMÝ.ODKAZ("E1:E" &DÉLKA(B2)));"");1);SUMA((KDYŽ(JE.ČISLO(HODNOTA(ČÁST($B$2;ŘÁDEK(NEPŘÍMÝ.ODKAZ("E1:E" &DÉLKA($B$2)));1)));ŘÁDEK(NEPŘÍMÝ.ODKAZ("E1:E" &DÉLKA($B$2)));0)>0)*(1)))
Text je v bunce B2citovat

Jirka323(30.10.2012 9:24)#010111

Pánové díky všem,
leč to makro fachá sice bezvadně, ale jen pro max 4 místné číslo...funkci jsem nerozchodil, píše mi to, že zadávám málo parametrů...:-(

Dík, Jcitovat

Poki(30.10.2012 9:38)#010112

...funkce je maticovy vzorec, musis ho ukoncit stiskem CTRL+SHIFT+ENTERcitovat

Anonym(30.10.2012 9:42)#010113

Bezva, fachá super, díky moccitovat

Dingo(30.10.2012 10:06)#010114

Pardon, ještě k té vlastní funkci, aby to fungovalo pro víc cifer, je třeba změnit definovaný výstupní formát, v 1.řádku vynechat "As Integer"
Function najdi_cislo(retez As String) a pak dát formát buňky na číslo, ne obecný. Jinak Poki, Megavzorec:

citovat

Michal98(21.4.2020 9:34)#046469

Děkuji moc za funkci taky se hodila.
Jen přidám, že pokud vaše číslo začíná nulou např. 003111 tak to nenavrátí text s prvními nuly. Příčina je ve funkci VAL(cifry).
Stačí pouze vymazat VAL, která první dvě nuly maže.

upravené na: najdi_cislo = cifry

Viz. https://www.techonthenet.com/excel/formulas/val.phpcitovat

elninoslov(21.4.2020 10:22)#046470

Tie 0 Vám zachová napr. takéto niečo:
=MID(A1;MATCH(TRUE;ISNUMBER(--(MID(A1;ROW(OFFSET($A$1;;;LEN(A1)));1)));0);SUM(1*ISNUMBER(--(MID(A1;ROW(OFFSET($A$1;;;LEN(A1)));1)))))
=ČÁST(A1;POZVYHLEDAT(PRAVDA;JE.ČISLO(--(ČÁST(A1;ŘÁDEK(POSUN($A$1;;;DÉLKA(A1)));1)));0);SUMA(1*JE.ČISLO(--(ČÁST(A1;ŘÁDEK(POSUN($A$1;;;DÉLKA(A1)));1)))))
alebo o niečo málo kratšie
=MID(A1;MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9};A1);FALSE));COUNT(1*MID(A1;ROW(OFFSET($A$1;;;LEN(A1)));1)))
=ČÁST(A1;MIN(IFERROR(NAJÍT({0;1;2;3;4;5;6;7;8;9};A1);NEPRAVDA));POČET(1*ČÁST(A1;ŘÁDEK(POSUN($A$1;;;DÉLKA(A1)));1)))
-textočíslo je v A1 (pozor výraz $A$1 nemeňte, to nieje odkaz na textočíslo)
-číslo v textočísle je spolu, nieje je rozdelené iným znakom (ani des. čiarkou)
-vzorec je to maticový, teda Ctrl+Shift+Enter
-ak hrozí, že v textočísle nebude žiadna číslica, tak si vzorec obalte ešte do
IFERROR(vzorec;"")citovat

Téma: Separace čísla z textové buňky

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje