Home

Text To Columns - Separate text from number

%3CLINGO-SUB%20id%3D%22lingo-sub-909314%22%20slang%3D%22en-US%22%3EText%20To%20Columns%20-%20Separate%20text%20from%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909314%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20would%20I%20convert%20Text%20to%20Columns%20that%20are%20in%20different%20lengths%20to%20set%20up%20a%20rule%20that%20the%20text%20in%20the%20column%20would%20start%20wehre%20a%20new%20column%20would%20be%20added%3F%3C%2FP%3E%3CP%3EIn%20addition%20to%20this%2C%20how%20would%20I%20keep%20the%20leading%20number%20to%20move%20to%20the%20next%20column%20as%20well%3F%3C%2FP%3E%3CP%3EAnd%20how%20would%20I%20keep%20the%20leading%20%230%20if%20the%20number%20starts%20with%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20839px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F137207i3EF33AE91FCF254F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Text%20to%20Column.PNG%22%20title%3D%22Text%20to%20Column.PNG%22%20%2F%3E%3C%2FSPAN%3Ea%200%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-909314%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909451%22%20slang%3D%22es-ES%22%3ERe%3A%20Text%20To%20Columns%20-%20Separate%20text%20from%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909451%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424568%22%20target%3D%22_blank%22%3E%40KellyPoling%3C%2FA%3E%26nbsp%3B%20I'm%20sorry%20it's%20in%20Spanish.%26nbsp%3B%20(%3B%C2%AC)%5D%20%26gt%3B%3C%2FP%3E%3CP%3EStepsOperationObservations%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Estep%201%3C%2FTD%3E%3CTD%3EInsert%20row%20in%20A1%3C%2FTD%3E%3CTD%3ESelect%201%3A1%20and%20press%20Ctrl%2B%5B%2B%5D%20%22Sum%20sign%22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%202%3C%2FTD%3E%3CTD%3EAdd%20column%20name%3C%2FTD%3E%3CTD%3EIn%20A1%20type%20the%20name%20%22Auxiliary%22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%203%3C%2FTD%3E%3CTD%3ESelect%20A2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%204%3C%2FTD%3E%3CTD%3EConverts%20to%20Excel%20Table%3C%2FTD%3E%3CTD%3ECrtl%20%2B%20(plus%20sign%20%22%2B%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%205%3C%2FTD%3E%3CTD%3ECheck%20box%20has%20headers%20-%20True%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%206%3C%2FTD%3E%3CTD%3EOK%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%207%3C%2FTD%3E%3CTD%3EName%20the%20table%3C%2FTD%3E%3CTD%3EOn%20the%20Table%20Layout%20tab%2C%20Section%20Properties%2C%20Name%20Box%20(MyTable)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%208%3C%2FTD%3E%3CTD%3EAdd%20auxiliary%20culumna%3C%2FTD%3E%3CTD%3EIn%20cell%20B1%20type%20Auxiliary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%209%3C%2FTD%3E%3CTD%3EEScribe%20formula%20in%20B2%3C%2FTD%3E%3CTD%3EIF(NUMBER(LEFT(%5B%40TablaDatos%5D%3B1)*1)%3B%22%20TxtAux%20%22%5B%40TablaDatos%5D%3B%20%5B%40TablaDatos%5D)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2010%3C%2FTD%3E%3CTD%3ECreate%20query%3C%2FTD%3E%3CTD%3EOn%20the%20Data%20tab%2C%20Get%20and%20Transform%20from%20a%20Table%20or%20Range%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2011%3C%2FTD%3E%3CTD%3EPower%20Query%20window%20opens%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2012%3C%2FTD%3E%3CTD%3ESelected%20Auxiliary%20Column%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%20%22Split%20Column%20by%3A%20Character%20Transition%22%3CBR%20%2F%3E-%20Table.SplitColumn(-%22Changed%20Type%22%2C%3CBR%20%2F%3E%22Auxiliary%22%2C%20Splitter.SplitTextByCharacterTransition((c)%20.%26gt%3B%3CBR%20%2F%3Enot%20List.Contains(-%220%22..%22%209%22%2C%22-%2C%20c)%2C%220%22.%22%209%22%2C%22%20%22Auxiliary.1%22%2C%20%22Auxiliary.2%22%2C%22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2013%3C%2FTD%3E%3CTD%3ESplit%20column%20from%20non%20number%20to%20number%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2014%3C%2FTD%3E%3CTD%3ESelect%20Auxiliary%20column.2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2015%3C%2FTD%3E%3CTD%3ESplit%20column%20from%20number%20to%20non-number%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%20%22Split%20Column%20by%3A%20Character%20Transition1%22%3CBR%20%2F%3Es%20Table.SplitColumn(%22Split%20Column%20by%3A%20Character%3CBR%20%2F%3ETransition%22%2C%20%22Auxiliary.2%22%2C%20Splitter.SplitTextByCharacterTransition(%220%22.%22%22%209%22%2C%20(c)%20%22not%20List.Contains%22%209%22%2Cc))%2C%20%22Auxiliary.2.1%22%2C%20%22Auxiliary.2.2%22-)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2016%3C%2FTD%3E%3CTD%3EClose%20and%20load%20in%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2017%3C%2FTD%3E%3CTD%3ESelect%20Table%26gt%3BSame%20Table%26gt%3BCell%20D1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2018%3C%2FTD%3E%3CTD%3ENow%20you'll%20know%20what%20you%20want%20to%20do%20with%20the%20data%22%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2019%3C%2FTD%3E%3CTD%3EIf%20you%20add%20new%20records%20to%20MyTable%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2020%3C%2FTD%3E%3CTD%3ESelect%20any%20cell%20in%20MyTable2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2021%3C%2FTD%3E%3CTD%3ELeft%20mouse%20button%20and%20select%20Update%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Estep%2022%3C%2FTD%3E%3CTD%3EAnd%20%22Magic%20happens%22%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909455%22%20slang%3D%22es-ES%22%3ERe%3A%20Text%20To%20Columns%20-%20Separate%20text%20from%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909455%22%20slang%3D%22es-ES%22%3E%3CP%3EGood%20morning%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424568%22%20target%3D%22_blank%22%3E%40KellyPoling%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20910px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F137226iD3976DE001AC537B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22RespAPoly.png%22%20title%3D%22RespAPoly.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909578%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20To%20Columns%20-%20Separate%20text%20from%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424568%22%20target%3D%22_blank%22%3E%40KellyPoling%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20second%20question%20bit%20easier%20-%20just%20assign%20Text%20type%20to%20all%20resulting%20column%20(with%20Text%20to%20Columns%20on%203rd%20step%20of%20the%20wizard%20select%20all%20columns%20and%20assign%20Text).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20separation%20is%20harder%2C%20afraid%20some%20manual%20work%20will%20be%20required%20in%20any%20case.%20First%2C%20the%20logic%20of%20separation%20is%20not%20formalized.%20If%2C%20for%20example%2C%20you'd%20like%20to%20separate%3C%2FP%3E%0A%3CP%3ECasillero%20del%20Diablo%20Sauvignon%20Blanc%20Reserva%20750%20ml%20on%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECasillero%20del%20Diablo%20%7C%26nbsp%3BSauvignon%20Blanc%20Reserva%20%7C%20750%20ml%3C%2FP%3E%0A%3CP%3Eyou%20can't%20say%20that%20text%20shall%20be%20split%20on%20each%203rd%20and%207th%20space%20since%20for%20each%20record%20this%20logic%20is%20different.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20wrap%20all%20brands%20and%20name%20by%20apostrophes%20%2C%20e.g%20Ctrl%2BH%20and%20Replace%20All%26nbsp%3BCasillero%20del%20Diablo%20on%20%22Casillero%20del%20Diablo%22.%20With%20splitting%20on%20columns%20above%20will%20be%20treated%20as%20one%20word.%20But%20that%20could%20be%20a%20lot%20of%20work%20plus%20punctuation%20of%20names%20is%20different%20(%26nbsp%3B%3CEM%3ECasillero%20del%20Diablo%3C%2FEM%3E%20and%26nbsp%3B%3CEM%3ECasilleroDel%20Diablo%3C%2FEM%3E%3B%20%3CEM%3E750ml%3C%2FEM%3E%20and%20%3CEM%3E750%20ml%3C%2FEM%3E).%3C%2FP%3E%3C%2FLINGO-BODY%3E
KellyPoling
New Contributor

How would I convert Text to Columns that are in different lengths to set up a rule that the text in the column would start wehre a new column would be added?

In addition to this, how would I keep the leading number to move to the next column as well?

And how would I keep the leading #0 if the number starts with Text to Column.PNGa 0?

3 Replies

@KellyPoling  Lo siento es en Español.  (;¬)]>

PasosOperaciónObservaciones

paso 1Insertar fila en A1Selecciona 1:1 y presiona Ctrl+[+] "Signo de suma"
paso 2Añade nombre de columnaEn A1 escribe el nombre "Auxiliar"
paso 3Selecciona A2 
paso 4Convierte en Tabla ExcelCrtl + (signo de suma "+")
paso 5Verifica casilla tiene encabezados = True 
paso 6Aceptar 
paso 7Asigna nombre a la tablaEn la pestaña Diseño Tabla => Sección Propiedades => Casilla de nombre (MiTabla)
paso 8Añade culumna auxiliarEn la celda B1 escribe Auxiliar
paso 9EScribe fórmula en B2=SI(ESNUMERO(IZQUIERDA([@TablaDatos];1)*1);"TxtAux "&[@TablaDatos];[@TablaDatos])
paso 10Crear consultaEn la pestaña Datos => Obtener y Transformar => Desde una Tabla o Rango
paso 11Se abre ventana Power Query 
paso 12Seleciona Columna auxiliar   #"Dividir columna por: transición de caracteres"
= Table.SplitColumn(#"Tipo cambiado", "Auxiliar",
Splitter.SplitTextByCharacterTransition((c) =>
not List.Contains({"0".."9"}, c), {"0".."9"}), {"Auxiliar.1", "Auxiliar.2"}),
paso 13Dividir columna de no número a número 
paso 14Selecciona columna Auxiliar.2 
paso 15Dividir columna de número a no número   #"Dividir columna por: transición de caracteres1"
= Table.SplitColumn(#"Dividir columna por: transición de caracteres",
"Auxiliar.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Auxiliar.2.1", "Auxiliar.2.2"})
paso 16Cerrar y cargar en 
paso 17Selecciona Tabla => Misma Tabla => celda D1 
paso 18Ahora Tu sabrás que quieres hacer con los datos" 
paso 19Si añades nuevos registros a MiTabla 
paso 20Selecciona cualquier celda de MiTabla2 
paso 21Con el botón izquierdo del Mouse Y selecciona Actualizar 
paso 22Y "La magia ocurre" 

Buen día, @KellyPoling 

 
 

RespAPoly.png

@KellyPoling 

With second question bit easier - just assign Text type to all resulting column (with Text to Columns on 3rd step of the wizard select all columns and assign Text).

 

With separation is harder, afraid some manual work will be required in any case. First, the logic of separation is not formalized. If, for example, you'd like to separate

Casillero del Diablo Sauvignon Blanc Reserva 750 ml on 

Casillero del Diablo | Sauvignon Blanc Reserva | 750 ml

you can't say that text shall be split on each 3rd and 7th space since for each record this logic is different.

 

You may wrap all brands and name by apostrophes , e.g Ctrl+H and Replace All Casillero del Diablo on "Casillero del Diablo". With splitting on columns above will be treated as one word. But that could be a lot of work plus punctuation of names is different ( Casillero del Diablo and CasilleroDel Diablo; 750ml and 750 ml).

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies