Question Comment convertir des données en hauteur dans plusieurs formats en pouces?


J'ai une colonne avec plusieurs milliers de hauteurs, dans différents formats, que je voudrais convertir en pouces.

Exemples de données réelles:

5 pi 2 po

6 pieds 3 pouces

6ft 3 pouces

6 pieds 3 "

5 * 8

5.3

5'10 "

5 71/2

Je pense qu'il y a un moyen d'obtenir les pieds dans une colonne et les pouces dans une seconde pour calculer les pouces. Les pieds semblent faciles, avec GAUCHE, mais je ne sais pas comment obtenir de manière fiable les pouces dans une autre colonne.

Quelle est la meilleure façon de le faire?


3
2018-02-08 20:26


origine


Un rapide googling m'a conduit à ce. Cela pourrait être utile. - Dan Hogan
Tous ces exemples de saisie de vos données sont-ils? 5 ft 2 in, 6 feet 3 inches, 5*8 (pour cinq pieds huit pouces?), 5.3 (Est-ce que 5 pieds trois pouces, ou 5,33333 pieds?) - BruceWayne
@ Kerbal314159 Oui, il s'agit d'exemples tirés des données réelles. Ceci a été entré par l'utilisateur et aucune restriction n'a été imposée au format - Mark Summers
Oh mon Dieu, cette dernière phrase est effrayante. Hm, ça va être plus compliqué. Pouvez-vous essayer et poster un exemple de toutes les différentes façons dont les hauteurs peuvent être saisies? - BruceWayne
@BruceWayne Je vais séparer par ";": 5ft; 5 pi 5 '; 5 pieds 2 pouces; 5 pi 2 pouces; 5'2 "; 5 '2"; 5ft 71/2 "; 5ft2in; 5ft 2in - Mark Summers


Réponses:


Vous pouvez utiliser MID ou Right pour les pouces ou vous pouvez convertir le texte en colonne. Il existe également une formule de conversion que vous pouvez utiliser après avoir séparé les données. Cette formule peut convertir tous les types d’unités.


0
2018-02-08 21:28



Je ne vois pas comment utiliser efficacement MID et RIGHT car il y a tellement de variation entre les points de données dans la même colonne. - Mark Summers


Ce sera une solution en plusieurs étapes et je le résoudrais en le divisant en plusieurs parties.
Je vais utiliser votre liste délimitée par des points-virgules (à partir des commentaires), plus une valeur supplémentaire: 5ft 7 1/2 ".

  1. Normalisez la liste autant que vous le pouvez en utilisant SUBSTITUTE:

    Col B: Replace "feet" with "ft"   =SUBSTITUTE(A2,"feet","ft")
    Col C: Replace "'" with "ft"      =SUBSTITUTE(B2,"'","ft")
    Col D: Replace "ft" and " ft" with " ft"
                                      =SUBSTITUTE(SUBSTITUTE(C2,"ft"," ft"),"  ft"," ft")
    Col E: Remove "inches"            =SUBSTITUTE(D2,"inches","")
    Col F: Remove "in"                =SUBSTITUTE(E2,"in","")
    Col G: Remove "                   =SUBSTITUTE(F2,CHAR(34),"")
    (CHAR(34) is the double quotes symbol, ")
    
    This can also be done with a long nested formula. I prefer simple.
    
  2. Utilisez SEARCH pour trouver la position de "ft"; si ce n'est pas là, écrivez "0"

    Col H: =IFERROR(SEARCH(" ft",G2,1),0)
    
  3. Calculer le nombre de pieds

    Col I: =IFERROR(VALUE(LEFT(G2,H2-1)),0)
    
  4. Les pouces sont plus durs, car ils peuvent contenir des fractions.
    Déterminez le texte représentant les pouces et découpez les premiers espaces à l'aide de TRIM.

    Col J: =IFERROR(MID(G2,H2+2,1000)),0)
    Col K: =TRIM(J2)
    
  5. Déterminez s'il y a des fractions dans le texte "pouces".
    S'il y a une fraction mixte, les nombres entiers doivent être séparés de la fraction par un espace (71/2 = 35,5; 7 1/2 = 7,5)
    Trouvez la position des espaces et des symboles "/" après eux.

    Col L: =IFERROR(SEARCH(" ",K2,1),0)
    Col M: =IFERROR(SEARCH("/",K2,L2+1),0)
    
  6. Trouvez le nombre de pouces ou (s'il y a une fraction mixte) le nombre entier de pouces.
    Si le pouce text = "", le nombre de pouces est 0.
    S'il n'y a pas de symbole "/", supposons que le nombre est un nombre décimal.
    S'il y a un symbole "/", le nombre entier est le nombre avant l'espace

    Col N: =IF(K2="",0,IF(M2=0,VALUE(K2),IFERROR(VALUE(LEFT(K2,L2-1)),0)))
    
  7. S'il y a un symbole "/", calculez le numérateur et le dénominateur.
    S'il n'y a pas de fraction, dénominateur = 1.

    Col O: Numerator    =IFERROR(VALUE(IF(M2=0,0,MID(K2,L2+1,M2-L2-1))),0)
    Col P: Denominator  =IFERROR(VALUE(IF(M2=0,1,MID(K2,M2+1,100))),1)
    
  8. Ajouter la liste des numéros

    Col Q: =I2*12+N2+O2/P2
    

Spreadsheet


0
2018-02-16 23:08