How to split text with multiple delimiters with Excel - Excel Tips and Tricks - video Dailymotion

RabiGurungXybernetics

Learn how to split text with multiple delimiters in Excel. We will be answering questions too. Can you have multiple delimiters in Excel, in another words how to use text to column delimiter multiple characters in Excel? We will also aspire to answer how do I separate multiple lines of text in one cell in Excel, and how do I split a text string into characters in Excel?   Splitting text (single delimiter type)  =TEXTSPLIT(A2,",")   Splitting text (multiple delimiter types)  =TEXTSPLIT(A3,{",",".",";",";;","/",":"})   Formula  =TRIM(TEXTSPLIT(A3,{",",".",";",";;","/",":"}))   Formula  =TRANSPOSE(TEXTSPLIT(A3,{",",".",";",";;","/",":"}))    Can you have multiple delimiters in Excel?,How to use text to columns delimiter multiple characters in Excel?,How do I separate multiple lines of text in one cell in Excel?,How do I split a text string into characters in Excel?,

Transcript

00:00If you have to split a text in a particular cell, you simply use a function called textSplit

00:04open parenthesis. The first argument will be the location where the text is. So this cell here,

00:11A2, and the second one will be the delimiter. Say double quote, comma, which is the common delimiter

00:16between the words here. And you say double quote, close parenthesis, and the text gets split nicely

00:21for you based on the delimiter of comma. But what if you have multiple different type of delimiters

00:27like double semicolon, single semicolon, forward slash, period, colon, you try to use the same

00:32formula like this, it doesn't work too well for you. So the way to get around this is this. So you're

00:37gonna take the second argument, you're gonna put them in curly braces. And inside the curly brace,

00:43you're gonna say comma, double quote, and list down all the possible delimiters that you might have.

00:49So I have that, and I have forward slash, comma, I have a period as well. And I also have a colon as

00:58well, like that. And then you try to apply to the rest of the rows, the same formula, and you can see

01:04it nicely splits the text for you. And if you have white spaces, leading white spaces, no problem,

01:11you can just surround the textSplit with a trim function like that. And you'll get rid of all the

01:18white spaces in front and behind. What if you want to list not horizontally, but you want to list it

01:23vertically like this. No worries, I'm going to copy this formula, paste it in here, it will list down

01:29in a horizontal format. And basically, what you're going to do is you're going to put it kind of

01:33surround the textSplit with a function called transpose. And it will list down in vertical way.

How to split text with multiple delimiters with Excel - Excel Tips and Tricks

Category

Transcript

Be the first to comment

Recommended