Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Looplist – Repeating months using formulas Basic Math
  • Show developer tab Excel User tips
  • 6174 Kaprekar’s constant Basic Math
  • Get 3rd Wednesday of month Basic Math
  • Progress bar in pure functions CHAR
  • Highlight dates dynamically AND
  • SimpleANBox Basic Math
  • Insert Blank rows into table using functions CHAR

Text-to-Columns, dynamically using formulas

Posted on April 3, 2018 By ANmar

I often use the technique of concatenating columns into 1 cell with separators.

Something like the CSV, 1 line that has all values for a single row (all columns for that row) into 1 text block.

And then, because of that, I need to extract that back, into table

I know there is “Text-to-Columns” in Excel, but I wanted that dynamically, so if source got changed, the columns output will be changed too.

So, here is the example, having three formulas in two columns.

Formula 1 in column E starting 11 down (Cell E10 just has the value of 1)

=IFERROR(SEARCH($D$8,$D$7,E10+1),"---")

Screenshot_2

 

Formula 2 in cell F11 only, to bring first item

=IFERROR(MID($D$7,E10,E11-1),"---")

Block2Rows6

 

Then, at last Formula 3, to get all additional columns from text block

=IFERROR(MID($D$7,E11+LEN($D$8),E12-E11-LEN($D$8)),"---")

Block2Rows5

 

I wanted to make it very clear and simple to show the case, data used in example is dummy data means nothing, just for sake of example.

 

Download spreadsheet
Excel Online version
IFERROR, Len, MID, SEARCH

Post navigation

Previous Post: Convert cell into textbox
Next Post: Worksheet name, dynamically using formula

Related Posts

  • Cuts string of list of items Formulas - combined functions
  • VALUE() of LEFT() Formulas - combined functions
  • Skills Grid Conditional Formatting
  • Repeated Offset Basic Math
  • Worksheet name, dynamically using formula CELL
  • SimpleANBox Basic Math

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • IFS (and IFNA) to avoid nested IFs Formulas - combined functions
  • Fixed length ID Formulas - combined functions
  • Multiple-Validations (connected to each other) Data Validation
  • Worksheet name, dynamically using formula CELL
  • Get monthly total from table with dates DATE
  • Middle name + 1st name Basic Math
  • Progress bar in pure functions CHAR
  • Calculate hours between two cells ABS

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme