Excel Split Text by Case: Methods and Formulas

Updated 5 November 2026 · Standard formula, shown below

Yes. Excel can split text when the letter case changes, but it has no built-in “split by case” command. For dependable results, use a formula in newer Excel versions or Power Query for repeatable data-cleaning; the right method depends on whether you want to separate words, extract uppercase runs, or split at every case transition.

What “split by case” means

Usually, the request means splitting a string where a lowercase letter is followed by an uppercase letter. For example, firstName becomes first and Name, and OrderID might become Order and ID. Those are not exactly the same rule: a basic case-change split treats each transition as a boundary, while a word-aware split tries to keep consecutive capitals together as an acronym.

Excel’s Text to Columns tool splits on specified delimiters, such as commas, spaces, or tabs. It does not recognize an uppercase letter as a delimiter by itself. Likewise, Flash Fill can infer a pattern from examples, but it is not a consistent rule-based case splitter. Use a formula or Power Query when the rule needs to work across many rows and be refreshed reliably.

Split at lowercase-to-uppercase transitions with a formula

In Microsoft 365 or Excel 2024, dynamic-array functions can identify character positions and return the pieces. Assume the original text is in A2. The following formula splits wherever a lowercase letter is immediately followed by an uppercase letter:

=LET(s,A2,n,LEN(s),p,SEQUENCE(n),c,MID(s,p,1),next,MID(s,p+1,1),TEXTSPLIT(REDUCE(s,SEQUENCE(MAX(1,n-1)),LAMBDA(a,i,IF(AND(CODE(MID(a,i,1))>=97,CODE(MID(a,i,1))<=122,CODE(MID(a,i+1,1))>=65,CODE(MID(a,i+1,1))<=90)),REPLACE(a,i+1,0,"|"),a))),"|")

This formula inserts a temporary vertical-bar delimiter at each lowercase-to-uppercase boundary and then splits on it. For example, firstName returns first and Name. It does not split at the boundary inside OrderID between r and I, but it does not know that ID is an acronym: a string such as userIDValue produces user, IDValue. The formula uses ASCII letter codes, so it is intended for ordinary English A–Z and a–z text, not every accented or non-Latin alphabet.

The formula spills its results into neighboring cells. Ensure those cells are empty; otherwise Excel may report a spill error. The temporary delimiter must not already occur in the source text, or it could create an unintended split. If your text can contain vertical bars, choose a delimiter you know is absent or use a Power Query transformation.

Use Power Query for a repeatable workflow

Power Query is usually a better choice when you need to clean a whole table, repeat the operation after new data arrives, or avoid long worksheet formulas. It is included in current desktop Excel editions, though menu names and availability can differ by platform and release.

Put the source values in a table or select the range, then choose Data and the option to get data from a table or range.
In the Power Query Editor, select the text column. Choose Add Column > Custom Column and create a transformation that inserts a delimiter before each uppercase character that follows a lowercase character.
Split the resulting column by the temporary delimiter using Split Column > By Delimiter. Select the appropriate split option, such as splitting at every occurrence.
Choose Close & Load to return the results to Excel. When the source table changes, refresh the query.

Power Query’s interface does not offer a universal “split by case” button. The custom transformation is needed to create a delimiter based on adjacent character case. If you are unfamiliar with Power Query’s M language, test the query on a small sample before applying it to the full dataset.

Choose the boundary rule before splitting

Case boundaries alone can produce surprising results around acronyms and digits. Decide what counts as a word before choosing a method. Common patterns include:

firstName → first | Name: split at lowercase-to-uppercase changes.
XMLParser → XML | Parser: keep an uppercase run together and split before the capital that begins a word followed by lowercase.
item2Code → item2 | Code: preserve digits with the preceding text, then split at the next lowercase-to-uppercase transition.

The simple formula above handles the first pattern, not the acronym-aware second pattern. For acronym-aware splitting, define a separate rule: a boundary occurs before an uppercase letter when the preceding character is uppercase and the following character is lowercase. That rule identifies the final capital in XMLParser as the start of Parser. More complicated conventions may require additional rules or a custom Power Query function.

Other options and when they fit

If you only have a few values, Flash Fill may be the quickest option: enter the desired split beside the source, provide a couple of representative examples, and use Data > Flash Fill if Excel recognizes the pattern. Inspect the output carefully. Flash Fill predicts results from examples; it does not enforce a specified character-case rule, so unusual strings can be handled inconsistently.

For text that has a known separator, such as first_Name or first-Name, use TEXTSPLIT with that actual delimiter rather than detecting letter case. This is simpler and more reliable. In older Excel versions without dynamic-array functions, use Power Query, Flash Fill for small jobs, or a more involved helper-column formula. Excel’s traditional Text to Columns command is useful only after a delimiter has been inserted or when the source already contains one.

Common problems to check

The formula is rejected: Your Excel version may not include functions such as LET, REDUCE, LAMBDA, or TEXTSPLIT. Use Power Query or an alternative suitable for that version.
Results do not spill: Clear cells to the right of the formula, including cells that look blank but contain spaces or formulas.
Acronyms split oddly: The formula’s rule is based on adjacent lowercase and uppercase letters, not dictionary knowledge or acronym recognition.
Some letters are missed: The sample formula checks basic English ASCII letter ranges. Accented letters and characters in other writing systems need a broader character-class approach.
Unexpected extra pieces appear: Check for the chosen temporary delimiter in the source text, and confirm whether digits or punctuation should count as boundaries.

Frequently Asked Questions

Can Excel split camelCase into separate words?
Yes. A formula or Power Query transformation can insert a separator before uppercase letters that follow lowercase letters. For camelCase, that gives camel and Case.

Can Text to Columns split by uppercase letters?
Not directly. Text to Columns splits on delimiters you specify. First create a delimiter at the case boundaries with a formula or Power Query, then split on that delimiter.

Can I keep acronyms such as XML together?
Yes, but a simple lowercase-to-uppercase rule is not enough for every acronym pattern. Use an acronym-aware boundary rule, such as splitting before the last capital in an uppercase run when it is followed by a lowercase letter.

Does the formula work in every Excel version?
No. The example uses newer dynamic-array functions. If your version does not support them, use Power Query, Flash Fill for a small checked dataset, or helper formulas designed for your version.

Will Excel split accented or non-English letters correctly?
Not necessarily with the sample formula, which checks basic A–Z and a–z character codes. For other alphabets, test a suitable Power Query or formula approach against the characters in your data.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.