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
• 29,080 points
96 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
• 53,920 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
• 53,920 points
137 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
• 53,920 points
155 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
• 53,920 points
248 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
• 53,920 points
83 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
• 53,920 points
164 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
• 53,920 points
129 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
• 53,920 points
141 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
• 53,920 points
147 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
• 53,920 points
118 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