Home

Get and Transform Excel 2016 - split a column with different delimiters in variable length text

%3CLINGO-SUB%20id%3D%22lingo-sub-386368%22%20slang%3D%22en-US%22%3EGet%20and%20Transform%20Excel%202016%20-%20split%20a%20column%20with%20different%20delimiters%20in%20variable%20length%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386368%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CBR%20%2F%3EIs%20it%20possible%20in%20the%20Get%20and%20Transform%20function%20to%20split%20columns%20by%20more%20than%20one%20delimiter%20in%20a%20variable%20length%20text%20string.%20For%20Example%3CBR%20%2F%3EPT1007%20WE%3A%2023-02-2017%20Name%3A%20J%20Jones%20ST%2025%20Units%3CBR%20%2F%3EPT1007%20WE%3A%2020-02-2017%20Name%3A%20Tom%20E%20Westward%20ST%20100%20Units%3CBR%20%2F%3Ehence%20in%20the%20above%20example%20split%20columns%2C%26nbsp%3B%20at%20the%20same%20time%20%2C%20by%20the%20following%20'different'%20delimiters%3CBR%20%2F%3EWE%3A%3CBR%20%2F%3EName%3A%3CBR%20%2F%3EST%3CBR%20%2F%3EUnits%3CBR%20%2F%3EGrateful%20for%20all%20your%20advice%3CBR%20%2F%3EIf%20you%20are%20able%20to%20assist%20can%20you%20reply%20with%20a%20screen%20shot%20please%20of%20the%20formula%20I%20need%20to%20enter%20so%20I%20can%20see%20what%20is%20needed%3CBR%20%2F%3EThankyou%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-386368%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386405%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20and%20Transform%20Excel%202016%20-%20split%20a%20column%20with%20different%20delimiters%20in%20variable%20length%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386405%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F258514%22%20target%3D%22_blank%22%3E%40Star12019%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20start%20to%20split%20from%20one%20delimiter%2C%20in%20formula%20bar%20you'll%20see%20as%20part%20of%20formula%20something%20like%3C%2FP%3E%0A%3CPRE%3ESplitter.SplitTextByDelimiter(%22del1%22%2C%20QuoteStyle.Csv)%2C%20%7B%22Col.1%22%2C%20%22Col2.2%22%7D)%3C%2FPRE%3E%0A%3CP%3EHere%20manually%20change%20function%20on%20SplitTextByAnyDelimeter%2C%20expand%20you%20delimiters%20as%20list%20as%20well%20as%20columns%20names%20and%20press%20Enter%2C%20it%20looks%20like%3C%2FP%3E%0A%3CPRE%3ESplitter.SplitTextBy%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3EAny%3C%2FSTRONG%3E%3C%2FFONT%3EDelimiter(%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%7B%3C%2FSTRONG%3E%3C%2FFONT%3E%22del1%22%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%2C%22del2%22%2C%22del3%22%7D%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%20QuoteStyle.Csv)%2C%20%7B%22Col.1%22%2C%20%22Col.2%22%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%2C%20%22Col.3%22%2C%20%22Col.4%22%3C%2FSTRONG%3E%3C%2FFONT%3E%7D)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Star12019
New Contributor

Hi
Is it possible in the Get and Transform function to split columns by more than one delimiter in a variable length text string. For Example
PT1007 WE: 23-02-2017 Name: J Jones ST 25 Units
PT1007 WE: 20-02-2017 Name: Tom E Westward ST 100 Units
hence in the above example split columns,  at the same time , by the following 'different' delimiters
WE:
Name:
ST
Units
Grateful for all your advice
If you are able to assist can you reply with a screen shot please of the formula I need to enter so I can see what is needed
Thankyou

1 Reply

Hi @Star12019 ,

 

You may start to split from one delimiter, in formula bar you'll see as part of formula something like

Splitter.SplitTextByDelimiter("del1", QuoteStyle.Csv), {"Col.1", "Col2.2"})

Here manually change function on SplitTextByAnyDelimeter, expand you delimiters as list as well as columns names and press Enter, it looks like

Splitter.SplitTextByAnyDelimiter({"del1","del2","del3"}, QuoteStyle.Csv), {"Col.1", "Col.2", "Col.3", "Col.4"})

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
34 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
9 Replies