Convertir une cellule sur une colonne.

[mp45]

Bonjour à tous,

J'ai un cellule avec une série de nombres comme celle-ci : " 22 35 18 65 2 224 " etc. J'aimerais que chacun de ces nombres apparaissent dans une cellule différente.

Je connais déjà l'outil Convertir présent dans le menu Données, mais celui-ci converti sur une ligne. Or mon problème est que j'ai plus de 256 nombres, et que il n'y a que 256 cellules sur une même ligne. Du coup, les derniers nombres passent à la trappe.

Je souhaiterais donc savoir s'il était possible de convertir de la même façon, mais cette fois sur une colonne, où le nombre de cellules disponibles est beaucoup plus important.

Merci d'avance ;-)

mp45

 

[job75]

Re : Convertir une cellule sur une colonne.

Bonjour mp45,

Sélectionner la cellule à convertir.

Puis exécuter cette macro (on peut lui affecter un raccourci clavier) :

Sub Convertir()
Dim s
s = Split(ActiveCell, " ")
ActiveCell.Resize(UBound(s) + 1) = Application.Transpose(s)
End Sub

A+

 

[skoobi]

Re : Convertir une cellule sur une colonne.

Bonsoir mp45,

ci-joint une solution par macro avec ce que j'ai compris.

Bonne soirée.

Edit: bonsoir job75 , aarfff, 1 mn de retard...

 

[Tibo]

Re : Convertir une cellule sur une colonne.

Bonsoir,

Ce que tu souhaites est possible avec une formule :

en A1, ta chaîne

Ailleurs, cette formule :

=STXT(SUBSTITUE(SUBSTITUE(" "&$A$1&" ";" ";"µ";LIGNES($1:1));" ";"¤";LIGNES($1:
1));TROUVE("µ";SUBSTITUE(SUBSTITUE(" "&$A$1&" ";" ";"µ";LIGNES($1:1));" ";"¤";
LIGNES($1:1)))+1;TROUVE("¤";SUBSTITUE(SUBSTITUE(" "&$A$1&" ";" ";"µ";LIGNES($1:
1));" ";"¤";LIGNES($1:1)))-TROUVE("µ";SUBSTITUE(SUBSTITUE(" "&$A$1&" ";" ";"µ";
LIGNES($1:1));" ";"¤";LIGNES($1:1)))-1)

Formule à recopier vers le bas

Bon... on doit pouvoir trouver plus simple...

@+

Edit : Bing ! Salut les amis Job75 et skoobi

@+

 

[Modeste]

Re : Convertir une cellule sur une colonne.

Bonsoir, les jeunes ... et les autres

Juste pour varier les plaisirs ... et sortir d'Excel: copier la cellule dans Word > Ctrl+H > rechercher [un_espace] > remplacer par ^p (marque de paragraphe) et re-copier-coller de Word vers Excel

Moins "propre" mais plus court que job et skoobi,

Range("a2:a500") = Application.Transpose(Split(Range("B1").Value, " "))

... ne reste qu'à effacer tous les #N/A

 

[mp45]

Re : Convertir une cellule sur une colonne.

Merci à tous ceux qui ont cherché. Pour les premières macro, ça marche en effet s'il n'y a qu'un espace entre les nombres. Seul soucis, c'est que j'en ai en fait 3 entre chaque. Je m'y connais assez peu en VBA, mais en remplaçant

s = Split(ActiveCell, " ")

par

s = Split(ActiveCell, " ")

, cela ne fonctionne pas.
(dans la macro de skoobi j'ai aussi essayer de remplacer le " " par " ", sans plus de résultats)
Il doit donc y avoir autre chose à remplacer, mais je ne trouve pas.

merci d'avance.

 

[david84]

Re : Convertir une cellule sur une colonne.

Bonsoir,

ça marche en effet s'il n'y a qu'un espace entre les nombres. Seul soucis, c'est que j'en ai en fait 3 entre chaque

il me semble qu'en adaptant la macro de Skoobi en plaçant 3 espaces au lieu de 1, cela fonctionne :

Sub trans()
Dim List As Variant, Lig As Long
List = Split(Range("B1").Value, "   ") ' 3 espaces entre les ""
List = Application.Transpose(List)
Lig = UBound(List, 1)
Range("A1:A" & Lig).Value = List
End Sub

Je pense que cela doit être la même chose pour les autres.

Concernant la formule de Tibo, elle fonctionne aussi correctement si elle est adaptée (mais je laisse à Tibo le soin de vérifier et de modifier si besoin ) :

=STXT($B$1;TROUVE("£";SUBSTITUE("   "&B$1&"   ";"   ";"£";LIGNES($1:1));LIGNES($1:1));TROUVE("¤";SUBSTITUE(SUBSTITUE("   "&B$1&"   ";"   ";"µ";LIGNES($1:1));"   ";"¤";LIGNES($1:1)))-TROUVE("£";SUBSTITUE("   "&B$1&"   ";"   ";"£";LIGNES($1:1));LIGNES($1:1))-1)

A+

 

[Staple1600]

Re : Convertir une cellule sur une colonne.

Bonsoir à tous


Pour info:
(dans le cas ou la string est séparé par un espace et un seul )

Range("a2:a500") = Application.Transpose(Split(Range("B1").Value, " "))

peut s'écrire

Range("a2:a500") = Application.Transpose(Split(Range("B1").Text))

Car par défaut le séparateur de Split est l'espace (ou Chr(32) )

cf ci-dessous l'extrait de l'aide de VBA

Facultatif. Caractère de chaîne utilisé pour identifier les limites de sous-chaîne. S'il est omis, le caractère espace (" ") est utilisé comme séparateur par défaut. Si l'argument delimiter est une chaîne de longueur nulle, un tableau à un élément contenant toute la chaîne expression est renvoyé.

 

[mp45]

Re : Convertir une cellule sur une colonne.

Cette ligne

List = Application.Transpose(List)

ne marche pas, ça m'affiche un message d'erreur

Erreur d'exécution '-2147417848(80010108)'

Erreur Automation
L'objet invoqué s'est déconnecté de ses clients

C'est ce que j'avais fait déjà tout à l'heure, remplacer les " " par " " ;-)

 

[ROGER2327]

Re : Convertir une cellule sur une colonne.

Bonsoir à tous

Pour régler le problème des espaces consécutifs, une petite modification de la procédure de job75 :

[COLOR=DarkSlateGray][B]Sub Convertit()
Dim s
  s = Split(WorksheetFunction.Trim(ActiveCell.Text), " ")
  ActiveCell.Resize(UBound(s) + 1).Value = WorksheetFunction.Transpose(s)
End Sub[/B][/COLOR]

ROGER2327
#4712

Dimanche 8 Sable 138 (Conception du Père Ubu (AJ), SS)
18 Frimaire An CCXIX
2010-W49-3T23:35:27Z

 

[job75]

Re : Convertir une cellule sur une colonne.

Bonjour Roger,

Dans le silence de mp45, merci Roger pour cette modification.

Et pour changer du VBA, une autre version de la formule de Tibo, à entrer en A2 et tirer vers le bas :

=STXT(SUPPRESPACE(A$1);TROUVE("µ";SUBSTITUE(" "&SUPPRESPACE(A$1)&REPT(" ";9999);" ";"µ";LIGNES(A$1:A1)));TROUVE("µ";SUBSTITUE(" "&SUPPRESPACE(A$1)&REPT(" ";9999);" ";"µ";LIGNES(A$1:A2)))-TROUVE("µ";" "&SUBSTITUE(" "&SUPPRESPACE(A$1)&REPT(" ";9999);" ";"µ";LIGNES(A$1:A1))))

REPT(" ";9999) évite les valeurs d'erreur #VALEUR! quand on tire la formule très bas...

A+

 

[mp45]

Re : Convertir une cellule sur une colonne.

Désolé pour le "silence", mais le forum était HS hier soir.

ça marche pas... Je rééssayerai ce w-e, c'est peut-être moi qui est juste mauvais^^

 

[ROGER2327]

Re : Convertir une cellule sur une colonne.

Re…

(…)

ça marche pas...
(…)

Voyez le classeur joint…
​

ROGER2327
#4725

Mercredi 11 Sable 138 (Nativité de Saint Grabbe, scherziste, SQ)
21 Frimaire An CCXIX
2010-W49-6T01:07:17Z

 

[job75]

Re : Convertir une cellule sur une colonne.

Bonjour le fil,

Il y a peut-être des "espaces" de code 160 dans les données à convertir !!

Dans ce cas, les remplacer par des espaces "normaux" (code 32) :

Sub Convertit()
Dim s
  s = Split(WorksheetFunction.Trim([COLOR="red"]Replace(ActiveCell.Text, Chr(160), " ") [/COLOR])," ")
  ActiveCell.Resize(UBound(s) + 1).Value = WorksheetFunction.Transpose(s)
End Sub

A+

 

[Paritec]

Re : Convertir une cellule sur une colonne.

Bonsoir Job Skoobi, tibo, Roger enfin tous,
Roger ta macro marche sous 2003 mais avec 2007 erreur de type
bonne soirée
Papou