Excel Function: TextSplit

Syntax

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

The TEXTSPLIT function syntax has the following arguments:

  • text       The text you want to split. Required. 

  • col_delimiter       The text that marks the point where to spill the text across columns.

  • row_delimiter       The text that marks the point where to spill the text down rows. Optional.

  • ignore_empty       Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.

  • match_mode    Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.

  • pad_with           The value with which to pad the result. The default is #N/A.

Remarks

If there is more than one delimiter, then an array constant must be used. For example, to split by both a comma, and a period, use =TEXTSPLIT(A1,{",","."}).

Examples

Copy the example data and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data. 

Split a name and a sentence by using a common delimiter.

Data

Dakota Lennon Sanchez

To be or not to be

Formulas

=TEXTSPLIT(A2, " ")

=TEXTSPLIT(A3, " ")

The following illustration shows the results.

Splits a name and sentence by a space delimiter 

Split the array constants in A2 into a 2X3 array.

Data

1,2,3;4,5,6

Formulas

=TEXTSPLIT(A2,",",";")

The following illustration shows the results.

Excel TextSplit Example 2 

Data

Do. Or do not. There is no try. -Anonymous

Formulas

=TEXTSPLIT(A2,".")

=TEXTSPLIT(A2,{".","-"})

=TEXTSPLIT(A2,{".","-"},,FALSE)

The following illustration shows the results.

Results of example 2

Data

Do. Or do not. There is no try. -Anonymous

Formulas

=TEXTSPLIT(A2,,".")

The following illustration shows the results.

Results of example 3

Data

Do. Or do not. There is no try. -Anonymous

Formulas

=TEXTSPLIT(A2,,{".","-"})

The following illustration shows the results.

Results of example 4

Data

Do. Or do not. There is no try. -Anonymous

Formulas

=TEXTSPLIT(A2,,{".","-"},TRUE)

The following illustration shows the results.

Results of Example 5

Tip      To remove the #NA error, use the IFNA function. Or add the pad_with argument.

Data

Do. Or do not. There is no try. -Anonymous

Formulas

=TEXTSPLIT(A2," ",".",TRUE)

The following illustration shows the results.

Results of example 6

Comments

Post a Comment

Popular posts from this blog

[Topic] Relation between two people in different stages

[Answer From Perplexity AI] 1. Seeing Someone Meaning:  This is often the earliest or most casual stage of romantic involvement. Implication:  You are spending time together and exploring romantic interest but without a formal commitment. Example:  "I'm seeing someone" suggests you are casually dating or getting to know a person romantically. 2. Dating Someone Meaning:  This usually implies a more intentional and regular romantic interaction than just "seeing someone." Implication:  You go on dates and are interested in potentially developing a deeper connection, but it might not yet be exclusive or serious. Example:  "I'm dating someone" means you are involved romantically and spending time together, but the level of commitment can vary. 3. In a Relationship Meaning:  This indicates a mutual commitment between two people. Implication:  You have agreed to be exclusive and consider yourselves a couple. Example:  "We are in a relationship" ...
Read more

why spring onions sold in UK withers very quickly upon regrowing in water

Image
  The spring onions (also called salad onions or scallions in the UK) you buy in supermarkets are not meant to live long-term — they’re essentially “cut-and-come-again” crops that have been forced to grow very quickly in perfect commercial conditions. When you try to regrow them at home in a jar of water, the leaves often collapse and wither quite dramatically (exactly like in your photo). Here’s why this happens in the UK (and elsewhere): 1. They’ve already used up most of their energy Supermarket spring onions are harvested when the bulb is still tiny. Almost all the stored energy is in the small white base, and the green leaves are very long and thin. Once cut from the field, that tiny bulb has to support those huge leaves withering leaves with no photosynthesis happening (because they were often stored in the dark). By the time you get them home, the bulb is already running on fumes. 2. UK supermarket varieties are chosen for shelf-life, not re-growability The varieties...
Read more

關閉 pagefile.sys 與 hiberfil.sys

Image
關閉 pagefile.sys pagefile.sys 是系統分頁檔，也可以說是虛擬記憶體，在以前記憶體很貴的時代，透過硬碟來虛擬記憶體的使用，在 Linux 上被稱為 SWAP ，在 Windows 上就是虛擬記憶體，系統預設沒有限制他的檔案大小，我只知道他越來越肥越來越肥，只是以前都沒有拿分頁檔開刀，這次終於受不了啦！ △ 從我的電腦右鍵內容，進入進階系統設定。 △ 系統內容 > 進階 > 效能 > 設定。 △ 效能選項 > 虛擬記憶體 > 變更，從這裡可看到我的虛擬記憶體容量為 16384 MB ，也就是使用 16 GB ，超級肥的啦！ △ 取消「自動管理所有磁碟的分頁檔大小」，可以自己設定大小，若是你的電腦沒有第二顆硬碟的話，建議還是可以設定虛擬記憶體。 △ 若是電腦本身記憶體很大，不需要虛擬記憶體也可以關閉分頁檔，之後還要點選「設定」才是設定完成。或是電腦有第二顆硬碟，可以選擇將分頁檔案放置在不是系統硬碟的地方。 △ 若是沒有使用系統分頁檔案，那麼在 Windows 當機的時候，可能就無法從追蹤當機的紀錄。 △ 我的電腦有第二顆硬碟而且空間容量比較大（ KINGSTON SSDNow V310 960GB ），於是設定第二顆硬碟存放虛擬記憶體，於是選到第二顆硬碟之後，選擇「系統管理大小」之後還要點選「設定」才是設定完成。 關閉 hiberfil.sys hiberfil.sys 這檔案也很佔空間，這個是系統進入休眠時所使用的檔案，系統休眠時會將記憶體內的東西都先存到硬碟之內，系統開啟時再從硬碟呼喚出來使用，所暫存的檔案就是 hiberfil.sys ，像我使用電腦幾乎都沒有在使用休眠，理論上可以捨去這個檔案所佔據的空間，我電腦中的 hiberfil.sys 佔據了 6GB 之多，也是非常肥的一個檔案。 △ 從搜尋輸入 cmd 找到命令提示字元，右鍵以系統管理員身分執行。 △ 接著輸入「 powercfg -h off 」就可以直接關閉休眠模式， hiberfil.sys 檔案也會瞬間消失。 △ 原本系統空間已經用盡，關閉 pagefile.sys 與 h...
Read more