extragere caractere si cifre dintr-o celula

Informatii despre cum se utilizeaza Microsoft Excel 2003. Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
catalinho
Mesaje: 191
Membru din: Dum Feb 21, 2010 11:30 am

extragere caractere si cifre dintr-o celula

Mesaj de catalinho » Vin Ian 20, 2012 9:15 pm

salut,

Am si eu urmatoarea problema: In excelul atasat am o coloana cu niste litere si cifre. Pt sortarea corecta ar trebui sa extrag intr-o coloana litera si intr-o alta coloana cifra respectiva.
Am lucrat pana acum astfel:
In col Lit Bilblioraft=Left(1; text) -merge bine acesta functie intrucat imi returneaza corect litera
In col Nr Biblioraft am folosit functia Right sa-mi returneze 2 caractere doar ca am unele unde imi returneaza de ex C2.Pe acestea trebuie sa le modific manual. Totodata, doresc alta solutie intrucat mai apar spatii intre litera si cifra si imi va returna si spatiul.
M-am gandit sa folosesc functia If doar ca nu stiu cum sa definesc conditiile.


Va multumesc
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: extragere caractere si cifre dintr-o celula

Mesaj de IPP » Vin Ian 20, 2012 9:34 pm

Buna seara

Pentru C2 incercati:

Cod: Selectaţi tot

=MID(SUBSTITUTE(A2;" ";"");2;2)*1
Formula pe care sa o copiati pe coloana in jos

Acel *1 de la sfarsit are menirea de a converti valoarea rezultata (care la astfel de formule este text) in numar. Daca doriti il puteti inlatura

IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

catalinho
Mesaje: 191
Membru din: Dum Feb 21, 2010 11:30 am

Re: extragere caractere si cifre dintr-o celula

Mesaj de catalinho » Vin Ian 20, 2012 9:55 pm

Va multumesc f mult pt rasp si promptitudine

Am inteles ce face formula. intai imi va sterge spatiile dintre caractere, dupa care va extrage toate caracterele incepand de la al doilea.
Frumos, si in acelasi timp usor.

Mai demult am mai intalnit o problema ceva de genu:
aveam niste CUI-uri introduse eronat:
RO123456
R12345
RO 12345

Se pot extrage cifrele din astfel de celule?

Va multumesc

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: extragere caractere si cifre dintr-o celula

Mesaj de IPP » Vin Ian 20, 2012 10:02 pm

Corect... partial. Va extrage doar 2 caractere incepand cu al doilea. Dar daca aveti de extras mai multe, puteti edita in formula ;)

Vedeti daca va ajuta subiectul: Gasire pozitie prima cifra dintr-o inregistrare alfanumerica

Daca nu, incercati sa atasati un fisier exemplu si mai multe detalii

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: extragere caractere si cifre dintr-o celula

Mesaj de IPP » Vin Ian 20, 2012 10:57 pm

Buna seara

Cred ca sunt sanse sa obtineti prin formule o aranjare a informatiei propice sortarii fara a mai fi nevoie de 1,2 sau chiar 3 coloane suplimentare. Puteti incerca o formula (in fisierul meu exemplu e in B2) de genul:

Cod: Selectaţi tot

=LEFT(A2;1)&TEXT(MID(SUBSTITUTE(A2;" ";"");2;3)*1;"000")
Practic va creaza un cod format din extragerea (informatiei din celula A2) primului caracter din stanga si unui numar obtinut asa cum am spus in mesajul anterior. In plus se mai adauga niste 0 in fata partii numerice astfel incat rezultatul final sa aiba acelasi numar de caractere.

Cu placere
IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Catalin B.
Moderator
Moderator
Mesaje: 813
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: extragere caractere si cifre dintr-o celula

Mesaj de Catalin B. » Sâm Ian 21, 2012 9:49 am

Se mai poate folosi o functie personalizata, care sa extraga cifrele dintr-o caseta ce contine si text si cifre.

Cod: Selectaţi tot

Function ExtrageNumar(rCell As Range, _
     Optional CuZecimale As Boolean, Optional Negative As Boolean) As Double


Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
    
    sText = rCell
    If CuZecimale = True And Negative = True Then
        strNeg = "-"
        strDec = "."
    ElseIf CuZecimale = True And Negative = False Then
        strNeg = vbNullString
        strDec = "."
    ElseIf CuZecimale = False And Negative = True Then
        strNeg = "-"
        strDec = vbNullString
    End If

    iLoop = Len(sText)
            For iCount = iLoop To 1 Step -1
                vVal = Mid(sText, iCount, 1)
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
                    i = i + 1
                    lNum = Mid(sText, iCount, 1) & lNum
                        If IsNumeric(lNum) Then
                            If CDbl(lNum) < 0 Then Exit For
                        Else
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
                        End If
                End If
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
            Next iCount

    ExtrageNumar = CDbl(lNum)

End Function
In foaia de lucru, functia se scrie simplu: =ExtrageNumar(F12)
Probleme să fie, că soluţii se găsesc...

catalinho
Mesaje: 191
Membru din: Dum Feb 21, 2010 11:30 am

Re: extragere caractere si cifre dintr-o celula

Mesaj de catalinho » Sâm Ian 21, 2012 10:37 am

Va multumesc amandurora pentru suportul acordat.
La prima problema voi folosi prima functie indicata de domnul IPP. Este simpla si utila pentru ceea ce am de facut.

Referitor la cea de doua:

Am citit link-ul indicat.M-am gandit sa aplic o formula cu cautarea primei cifre dupa care sa extraga tot ce gaseste dupa.
Nu am testat momentan, dar voi reveni cu formula.

Catalin.B

Imi cer scuze daca am procedat gresit cu respectivul cod dar imi da eroarea NAME.
Mentionez ca am copiat tot codul intr-un modul, dupa care am regasit funtia in InsertFunction.La deschiderea acesteia am vazut ca solicita introducerea mai multor date.probabil sunt obligatorii.
Am testat-o si in modul indicat =ExtrageNumar(A12), iar in A12 am introdus "RO23456"

Multumesc!

IPP
Moderator
Moderator
Mesaje: 4196
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: extragere caractere si cifre dintr-o celula

Mesaj de IPP » Sâm Ian 21, 2012 10:54 am

@catalinho

O eroare de acel tip apare de obicei cand numele functiei nu este recunoscut de Excel.
Eu am facut cateva teste cu acel cod scriind denumirea functiei de la tastatura si n-am avut probleme...

Catalin B.
Moderator
Moderator
Mesaje: 813
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: extragere caractere si cifre dintr-o celula

Mesaj de Catalin B. » Sâm Ian 21, 2012 11:00 am

Celelalte argumente sunt optionale, daca citesti cu atentie , si se pot utiliza daca in caseta analizata exista numere ce contin zecimale, si/sau numere negative( cu conditia ca daca numarul este negativ, semnul minus sa fie plasat in fata primei cifre din sirul text):
Function ExtrageNumar(rCell As Range, Optional CuZecimale As Boolean, Optional Negative As Boolean) As Double
Eroarea name poate apare daca nu sunt activate macrocomenzile . (am facut si o corectie la afisarea zecimalelor, nu functiona corect)
Anexez si un fisier exemplu, cu codul corectat.
Celelalte argumente se pot utiliza scriind formula in forma: =ExtrageNumar(E8;1;1) sau =ExtrageNumar(E8;TRUE;TRUE) pentru sir ce contine numar negativ cu zecimale. Daca este doar negativ si nu contine zecimale, al doilea argument nu trebuie sa lipseasca, dar va fi 0 sau FALSE.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Probleme să fie, că soluţii se găsesc...

catalinho
Mesaje: 191
Membru din: Dum Feb 21, 2010 11:30 am

Re: extragere caractere si cifre dintr-o celula

Mesaj de catalinho » Sâm Ian 21, 2012 11:31 am

functioneaza perfect.

Multumesc!
Week-end placut!

Închis

Înapoi la “Intrebari despre Excel 2003”