Advanced(?) column splitting

[Juan Sombrero]

Hi all,

I have a text column with variable length and want to split is into the last character and the "rest" (this rest is of variable length). It's easy to extract the last character, but then the original table will remain and I still don't have my "rest".

Anybody a tip on how to do this?

Thx in advance,
Juan

 

[Alan_P]

Hi Juan,

try;
=LEFT(A1,LEN(A1)-1)

Hope this helps,
Cheers,
Alan.

 

[Juan Sombrero]

Ok my Bet...I probably should have mentioned its in power query...

 

[ImkeF]

This is one way:
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "A", "B"),
#"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"A", each Text.Start(Text.From(_, "de-DE"), Text.Length(_)-1), type text}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Extracted First Characters", {{"B", each Text.End(Text.From(_, "de-DE"), 1), type text}})
in
#"Extracted Last Characters"

Duplicating you column and then taking the first x first & only the last one from the second column.

 

[billszysz]

Hi Juan
You can do that almost directly from UI with this step
SplitCol = Table.SplitColumn(#"Changed Type","A",Splitter.SplitTextByPositions({0, 1}, true),{"A", "B"})

The red part you have to put manually because UI delivers {"A.1", "A.2"}
Of course Imke's way is also good )

Regards