Forum Discussion
Numeric and alpha split Column
Hello,I seek to divide the contents of an alphanumeric column into two ciolonnes, exclusively digital moon, the other containing the digital part of the column.
Intial situation
| CompteNum | CompteLib |
| 411FIDUC | CLIENT 1 |
| 411WEBER | CLIENT 2 |
| 411MILHEM | CLIENT 3 |
| 4010FOURN1 | Fournisseur 1 |
| 4010FOURN1 | Fournisseur 1 |
| 4010FOUNR2 | Fournisseur 2 |
| 1010000000 | CAPITAL |
| 1013000000 | CAPITAL SOUSCRIT APPELE VERSE |
| 1190000000 | REPORT A NOUVEAU SOLDE DEBIT |
| 1200000000 | RESULTAT DE L'EXERCICE - BENEF |
Expected outcome
| CompteNum | CompteLib | CompteAuxNum | CompteNum2 |
| 411FIDUC | CLIENT 1 | FIDUC | 411 |
| 411WEBER | CLIENT 2 | WEBER | 411 |
| 411MILHEM | CLIENT 3 | MILHEM | 411 |
| 4010FOURN1 | Fournisseur 1 | FOURNI1 | 4010 |
| 4010FOURN1 | Fournisseur 1 | FOURNI1 | 4010 |
| 4010FOUNR2 | Fournisseur 2 | FOURNI2 | 4010 |
| 1010000000 | CAPITAL | 1010000000 | |
| 1013000000 | CAPITAL SOUSCRIT APPELE VERSE | 1013000000 | |
| 1190000000 | REPORT A NOUVEAU SOLDE DEBIT | 1190000000 | |
| 1200000000 | RESULTAT DE L'EXERCICE - BENEF | 1200000000 |
Thank you for the help you can give me
Philippe Muniesa
On the Home tab of the query editor, under Split Column choose Digit to Non-digit.
Regards,
Pat
Thank you,
thank you, I did not know this feature, very convenient. In the meantime, I had finished not arrive at the same result with different lines of code, which I allow myself to deliver here for information Although the built-in function is much simpler.
CompteNum is the cowhich contains the numerical alpha data to split
let
Source = fx_Bal_Tiers(Ouvre_FEC1),
#"Colonnes permutées" = Table.ReorderColumns(Source,{"CompAuxNum", "CompAuxLib", "CompteLib", "Debit", "Credit", "Solde", "CompteNum"}),
Remplace_esp_rien_Dans_NUM = Table.ReplaceValue(#"Colonnes permutées"," ","",Replacer.ReplaceText,{"CompteNum"}),
#"Type modifié" = Table.TransformColumnTypes(Remplace_esp_rien_Dans_NUM,{{"CompteNum", type text}}),
#"Added Custom" = Table.AddColumn(#"Type modifié", "Custom", each Text.PositionOfAny([CompteNum],{"A".."Z"})),
#"Type modifié1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
Nombre_Car_Chaine = Table.AddColumn(#"Type modifié1", "Longeur_CAR", each Text.Length([CompteNum])),
#"Inserted First Characters" = Table.AddColumn(Nombre_Car_Chaine, "First Characters", each Text.Start([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Type modifié2" = Table.TransformColumnTypes(#"Inserted First Characters",{{"First Characters", Int64.Type}}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié2", "test", each if [Custom] > 0 then [Custom] else [Longeur_CAR]),
#"Inserted Text Range" = Table.AddColumn(#"Colonne conditionnelle ajoutée", "Text Range", each Text.Middle([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"CompteNum", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Range", "Number"}})
in
#"Renamed Columns"Philippe Muniesa
2 Replies
On the Home tab of the query editor, under Split Column choose Digit to Non-digit.
Regards,
Pat
Thank you,
thank you, I did not know this feature, very convenient. In the meantime, I had finished not arrive at the same result with different lines of code, which I allow myself to deliver here for information Although the built-in function is much simpler.
CompteNum is the cowhich contains the numerical alpha data to split
let
Source = fx_Bal_Tiers(Ouvre_FEC1),
#"Colonnes permutées" = Table.ReorderColumns(Source,{"CompAuxNum", "CompAuxLib", "CompteLib", "Debit", "Credit", "Solde", "CompteNum"}),
Remplace_esp_rien_Dans_NUM = Table.ReplaceValue(#"Colonnes permutées"," ","",Replacer.ReplaceText,{"CompteNum"}),
#"Type modifié" = Table.TransformColumnTypes(Remplace_esp_rien_Dans_NUM,{{"CompteNum", type text}}),
#"Added Custom" = Table.AddColumn(#"Type modifié", "Custom", each Text.PositionOfAny([CompteNum],{"A".."Z"})),
#"Type modifié1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
Nombre_Car_Chaine = Table.AddColumn(#"Type modifié1", "Longeur_CAR", each Text.Length([CompteNum])),
#"Inserted First Characters" = Table.AddColumn(Nombre_Car_Chaine, "First Characters", each Text.Start([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Type modifié2" = Table.TransformColumnTypes(#"Inserted First Characters",{{"First Characters", Int64.Type}}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié2", "test", each if [Custom] > 0 then [Custom] else [Longeur_CAR]),
#"Inserted Text Range" = Table.AddColumn(#"Colonne conditionnelle ajoutée", "Text Range", each Text.Middle([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"CompteNum", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Range", "Number"}})
in
#"Renamed Columns"Philippe Muniesa