How to automate split by delimiter in Excel equivalent of SPLIT in gSheets

0 votes

I recently switched from using Google Sheets exclusively to using Excel, so I'm still getting used to several items being missing. I must divide cells in Excel using a space delimiter (" ").

I'm trying to figure out how to do this using an Excel formula. The Google Sheets equivalent of =SPLIT(#REF, "") in Excel. Although Excel includes a "Text to Columns" option, I would like a formula because I'm aiming to totally automate a project to lower the likelihood of human error.

Additionally, I need to maximize the number of splits to 4, so it ignores everything after the 4th split.

I've tried a few things, such as using

 =LEFT(#REF,FIND(" ",#REF)),=RIGHT(#REF,FIND(" ",#REF)), and =MID(#REF, SEARCH(" ",#REF) + 1, SEARCH(" ",#REF,SEARCH(" ",#REF)+1) - SEARCH(" ",#REF) - 1). 

The issue is, the number of spaces within the cell can vary. Please see an example table below:

ToSplit
Split #1
Split #2
Split #3
Split #4
Hello
Hello
World
World
Hello World
Hello
World
Hello World FOO BAR BAZ
Hello
World
FOO
BAR
This Data Wants To Be Different
This
Data
Wants
To

Is there any way to obtain this functionality within Excel, please? 

Dec 28, 2022 in Others by Kithuzzz
• 38,010 points
309 views

1 answer to this question.

0 votes

Multiple ways, one is to use FILTERXML():

enter image description here

Formula in B2:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[position()<5]"))

This assumes ms365's spilling dynamic arrays. However, you could also use, for example:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE($A2," ","</s><s>")&"</s></t>","//s["&COLUMN(A1)&"]"),"")

Drag down and right.


For more information on FILTERXML() and another custom SPLIT() function, see this Q&A.

answered Dec 29, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
423 views
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
409 views
0 votes
1 answer

How to get sum of all matches of HLOOKUP in Excel?

Consider: =SUMPRODUCT((A1:E1="apple")*(A2:E2)) To include more ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,420 points
2,029 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,420 points
397 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,420 points
605 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
451 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,420 points
602 views
0 votes
1 answer

How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,420 points
946 views
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
592 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP