Microsoft 365 Addition des nombres dans une cellule avec du texte

Dreyz · 16 Décembre 2020

Bonjour à tous,

Je tente en vain d'additionner des chiffres de ma colonne A qui contient des nombres et des lettres, vers ma colonne B
Exemple :
A1 :

benoit x 1, eric x 1, nathalie x 1, marc x 15

J'aimerais avoir en B = 18

A2 :

jean-jacques x 1, eric x 1, marie x 1

En B = 3

A3 :

jean-jacques x 1, eric x 5, marie x 1, marie x 1, marc x 1, sylvie x 1, , flavien x 5

En B = 15

Je cherche donc à trouver une formule pour additionner les chiffres se trouvant après "x ".
Je précise que je suis essentiellement sous google sheet, ayant testé une formule type (je débute sur les tableurs, j'avais trouvé cela sur le net)

=SOMMEPROD(SIERREUR(1*STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))

Mais qui ne prend pas en compte les valeurs supérieurs à 9. Exemple au lieu d'avoir d'avoir une somme de 18 en B1 , il me renvoie 9 (1+1+1+1+5).

Merci à vous,
Nicolas.

R@chid · 17 Décembre 2020

Re,

Staple1600 à dit:
Puis je me suis souvenu que j'avais des archives alors j'ai mis un masque et soulevé la poussière et bim!
=SOMME(SIERREUR((STXT(SUBSTITUE(SUBSTITUE(A1;",";"");" ";REPT(" ";NBCAR(A1)));1+NBCAR(A1)*(LIGNE(A$1:INDEX(A:A;NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))+(A1<>"")))-1);NBCAR(A1)))+0;0))
Il faudrait un volontaire sous XL2K10 pour la tester

On peut faire plus courte, en B1 :

VB:

=SOMME(SIERREUR(STXT(SUBSTITUE(A1;" ";REPT(" ";NBCAR(A1)));(LIGNE(INDIRECT("1:"&(NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";))+1)))-1)*NBCAR(A1);NBCAR(A1))*1;))

@ valider par Ctrl+Maj+Entrée
@ tirer vers le bas

Valide pour toutes les versions à partir de 2007.

Noter que sous Excel pour Microsoft 365 on n'a pas...

R@chid · 16 Décembre 2020

Bonjour,
pourrais-tu nous préciser ta version d'Excel ?

Dreyz · 16 Décembre 2020

Bonjour,
J'utilise la suite office 365. J'ai édité le poste.

R@chid · 16 Décembre 2020

Re,
ok, en B1 :

VB:

=SOMME(FILTRE.XML("<a><b>"&SUBSTITUE(A1;" ";"</b><b>")&"</b></a>";"//b"))

@ tirer vers le bas

Cordialement

JHA · 16 Décembre 2020

Bonjour à tous,

En utilisant Données/convertir, on peut sortir quelques résultats.

JHA

soan · 16 Décembre 2020

Bonjour Dreyz, le fil,

avec cette fonction personnalisée :

VB:

Option Explicit

Function Total(chn$) As Integer
  Dim s0, s1$, p%, k%, i As Byte
  s0 = Split(chn, ",")
  For i = 0 To UBound(s0)
    s1 = Trim$(s0(i)): p = InStrRev(s1, " ")
    k = k + Val(Mid$(s1, p + 1))
  Next i
  Total = k
End Function

soan

Staple1600 · 16 Décembre 2020

Bonsoir le fil, JHA, Nicolas, R@chid, soan

Dreyz à dit:
Bonjour à tous,
Je précise que je suis essentiellement sous google sheet, ayant testé une formule type (je débute sur les tableurs, j'avais trouvé cela sur le net)
Nicolas.

Alors Google Sheets ou Office 365
Car Google Sheets parle mal le VBA

Si au final, c'est Office 365, alors une autre façon de faire
(mais ce plus vraiment dans la sphère "débutant" * )
Test OK sur les exemples du 1er premier message.

VB:

Function SOMMECELL(vCell As String, Optional vP = "[^\d\+]") As Double
On Error GoTo O_ut
tmp = Replace(vCell, " x ", " + ")
    With CreateObject("vbscript.regexp")
    .Pattern = vP: .Global = -1:: SOMMECELL = Evaluate(.Replace(tmp, ""))
    End With
Exit Function
O_ut:
SOMMECELL = 0
End Function

Mode d'emploi
=SOMMECELL(A1)

* :NB: Dans ce cas, privilégie la formule de R@chid, parce qu'une formule c'est plus simple quand on débute avec Excel.

Dreyz · 16 Décembre 2020

Super, merci à tous, vos solutions ont résolu mon problème.
Encore merci.

Staple1600 · 16 Décembre 2020

Re

J'oubliais les MACistes
Donc une version qui fonctionnera sur les Mac.

VB:

Function SOMMECELL2(R As Range, Optional Sep As String = "x") As Double
Dim t As Variant, k&: t = Split(R, Sep)
For k = LBound(t) To UBound(t) Step 1: SOMMECELL2 = SOMMECELL2 + Val(t(k)): Next
End Function

Staple1600 · 16 Décembre 2020

Re

=>Dreyz
Tu n'as pas répondu à la question dans le message#7
Google Sheets en priorité ou Excel 365?

Dreyz · 16 Décembre 2020

Staple1600 à dit:
Re

=>Dreyz
Tu n'as pas répondu à la question dans le message#7
Google Sheets en priorité ou Excel 365?

Dans l'idéal, google sheet

R@chid · 16 Décembre 2020

Bonjour @ tous,
j'ai pas fait attention que tu es sous GoogleSheet, j'ai vu "Autre" c'est pour cela j'ai demandé une précision.
Sous GoogleSheet, en B1 :

VB:

=SUM(SPLIT(A1;" "))

@ tirer vers le bas

Sinon, ma première formule fonctionne je pense sous Excel 2013, 2016 et 2019, si quelqu'un peut faire un test et me tenir informé, je voudrais juste être sûr que FILTRE.XLM() est disponible sous ces versions.

Cordialement

Staple1600 · 16 Décembre 2020

Re

C'est bien ce qu'il me semblait.
Sous réserve de vérification, les solutions à base de VBA publiées dans le fil ne devraient pas fonctionner avec Google Sheets.

Staple1600 · 16 Décembre 2020

Re,

=>R@chid

Test OK sur Excel 2013
Mais où vas-tu chercher ces formules?
Dans la 4ième dimension ?

En tout cas, chapeau bas pour cette formule du 3ième type !!!!

mapomme · 16 Décembre 2020

R@chid à dit:
je voudrais juste être sûr que FILTRE.XLM() est disponible sous ces versions.

Voir ICI ce que dit Krosoft.

Microsoft 365 Addition des nombres dans une cellule avec du texte

Dreyz

XLDnaute Nouveau

R@chid

R@chid

XLDnaute Barbatruc

Dreyz

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

Pièces jointes

soan

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

Dreyz

XLDnaute Nouveau

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Dreyz

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Discussions similaires