Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Excel Functions = XL f(x)s XLfxs
  • Limited Ceiling Basic Math
  • Links between two workbooks – dynamically using functions INDIRECT
  • Formula Beautifier Excel User tips
  • Why I ran from =INDIRECT() function Formulas - combined functions
  • Dynamic selection list ActiveX controls
  • Sorting with functions only CHOOSE
  • Recover the unrecoverable Excel User tips

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

  • New function IFERROR, finally IFERROR
  • VALUE() of LEFT() Formulas - combined functions
  • Worksheet name, dynamically using formula CELL
  • Extract current (Active) workbook name CELL
  • Always get 1st name (or last) Formulas - combined functions
  • Cuts string of list of items Formulas - combined functions

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
  • Convert cell into textbox Excel User tips
  • Recover the unrecoverable Excel User tips
  • Expenses calendar, FaCal Basic Math
  • Excel Limits Excel User tips
  • CamelCase to Camel Case CHAR
  • Excel ad from early 90s Non-functions
  • Get 3rd Wednesday of month Basic Math
  • LastSunday, LastSaturday Date and Time

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme