• Status: Solved
• Priority: Medium
• Security: Public
• Views: 388

Excel, parse strings into columns from a list of offsets (columns widths) and column names

How to parse strings into columns  from a list of offsets (columns widths) and column names.  The rows of text are limited to a hundred or so.  The source string length is <1000 characters,   there are about 40 columns of vary widths.
EE-Excel-parse.xls
0
AndyPandy
3 Solutions

Commented:
On sheet 3, first row, formula starting in A1, copy across

=INDEX(Sheet2!\$B\$1:\$B\$40,COLUMN()+1)

On sheet3, second row, formula starting in A2, copy across and down:
=MID(INDEX(Sheet1!\$A:\$A,ROW()-1),INDEX(Sheet2!\$A:\$A,MATCH(A\$1,Sheet2!\$B:\$B,0)),INDEX(Sheet2!\$A:\$A,1+MATCH(A\$1,Sheet2!\$B:\$B,0))-INDEX(Sheet2!\$A:\$A,MATCH(A\$1,Sheet2!\$B:\$B,0)))

Thomas
0

Commented:
I came up with this while Thomas was posting.  Not the most elegant thing, but it does appear to preempt errors if the source strings are not long enough.

The operative formula goes in Sheet3!A2, and is copied across and down:

=MID(Sheet1!\$A2,INDEX(Sheet2!\$A:\$A,MATCH(A\$1,Sheet2!\$B:\$B,0),1),MAX(0,IFERROR(INDEX(Sheet2!\$A:\$A,MATCH(B\$1,Sheet2!\$B:\$B,0),1),LEN(Sheet1!\$A2)+1)-INDEX(Sheet2!\$A:\$A,MATCH(A\$1,Sheet2!\$B:\$B,0),1)))

Q-28014774.xlsx
0

Commented:
This formula should also work for Sheet3 A2 copied across and down

=MID(LEFT(Sheet1!\$A2,MOD(INDEX(Sheet2!\$A:\$A,MATCH(A\$1,Sheet2!\$B:\$B,0)+1)-1,1000)),SUMIF(Sheet2!\$B:\$B,A\$1,Sheet2!\$A:\$A),1000)

regards, barry
0

Author Commented:
Thanks, you guys are genius'sssss
Thomas, nice but leaves #value when string too short.
Matt,  #Name?,  perhaps my Excel 2003 doesn't support your solution?
Barry, Nice short and sweet and no error.  Still trying to figure out how it does this.
Thanks guys!
0

Commented:
AndyPandy,

Glad to help, and not surprised that once again barry wins the formula derby :)

One of the functions I used, IFERROR, debuted in Excel 2007, so yes, in Excel 2003 and earlier you will get a #NAME error if you use it.

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.