Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Get nth match with INDEX / MATCH in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Two-way lookup with VLOOKUP in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Perform case-sensitive Lookup in Excel

Data Analysis Examples

  • Conditional Formatting Data bars Examples in Excel
  • Get column name from index in Excel Table
  • How to sum a total in multiple Excel tables
  • Randomize/ Shuffle List in Excel
  • How to Create Thermometer Chart in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation must not contain
  • Excel Data validation with conditional list
  • Excel Data validation must begin with
  • Excel Data validation exists in list

How to convert text string to array in Excel

by

To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate text at the character level.

 Formula

{=MID(string,ROW(INDIRECT("1:"&LEN(string))),1)}

Note: this is an array formula and must be entered with control + shift + enter.

Worked Example:   Find longest string in column in Excel

Explanation

In the example shown, the formula in C5 is:

{=MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)}

How this formula works

Working from the inside out, the LEN function calculates the length of the string, and this is joined by concatenation to “1:”, creating a text range like this: “1:3”

Worked Example:   Find longest string with criteria in Excel

This text is passed into INDIRECT, which evaluates the text as a reference and returns the result to the ROW function. The ROW function returns the rows contained in the reference in an array of numbers like this:

{1;2;3}

Notice we have one number for each letter in the original text.

Worked Example:   Basic outline numbering in Excel

This array goes into the MID function, for the start_num argument. The text comes from column B, and the number of characters is hardcoded as 1

Finally, with multiple start numbers, MID returns multiple results in an array like this.

{"R";"e";"d"}

Post navigation

Previous Post:

How to convert numbers to text using TEXT function in Excel

Next Post:

How to count keywords in a range of cell

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Find function vs Search function in Excel
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to convert text string to array in Excel
  • How to count number of characters of text in a cell in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • Calculate series of dates by workdays in Excel
  • Get days, months, and years between dates in Excel
  • Convert date to text in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Convert Excel time to decimal hours in Excel
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan principal payments in Excel
  • How to Calculate Tax Rates in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles