< návrat zpět

MS Excel


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

Zaslal/a 29.10.2012 15:13

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ěď >

#010098
avatar
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
#010102
avatar
Co si napsat vlastní funkci, je to sice jako makro, ale zase člověk aspoň vidí vzoreček v buňce 5 .
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: zip10102_najdi_cislo.zip (12kB, staženo 46x)
citovat
icon #010104
Poki
...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
#010111
avatar
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
icon #010112
Poki
...funkce je maticovy vzorec, musis ho ukoncit stiskem CTRL+SHIFT+ENTERcitovat
#010113
avatar
Bezva, fachá super, díky moccitovat
#010114
avatar
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: 9citovat
#046469
avatar
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
#046470
elninoslov
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

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura III

Oblíbený formulář Faktura byl vylepšen a rozšířen. Formulář faktura III
Více se dočtete zde.

Aktivní diskuse

Zaloha zošitov okrem hdd a nas

milada • 23.9. 22:03

VLOOKUP jen pro poslední přidané řádky

Jiří497 • 23.9. 21:59

Zaloha zošitov okrem hdd a nas

lugr • 23.9. 21:47

Zaloha zošitov okrem hdd a nas

Milada • 23.9. 21:28

zlucenie buniek do jedneho

pavol14pavol • 23.9. 21:11

zlucenie buniek do jedneho

robert13 • 23.9. 19:23

vyzisti pocet dni s datumu

robo • 23.9. 19:01