Adding blank columns

Hi,
Below is the function provided by Glen at https://www.excelforum.com/excel-for...ml#post5905252 #Post 31

Please Login or Register  to view this content.

How to add blank columns in between Choosecols(1,2,3,5,4,6),i.e like this > Choosecols(1,2,3,"","","","",5,"",4,6).

You could go for something like this:

Formula:

Please Login or Register  to view this content.

'\' Column separator in my region.

Hi Gregb11,

It did ok with the file. Translation of formulas is a thing...

Originally Posted by Gregb11

=LET(f,LET(
A,Sheet1!A6:R1235,
B,CHOOSECOLS(FILTER(A,(INDEX(A,,4)<>"")*(INDEX(A,,18)+INDEX(A,,17)<>0)),2,3,4,11,17,18),
C,IFERROR(1/(1/CHOOSECOLS(B,1,2)),""),
D,MAP(INDEX(B,,3),LAMBDA(z,TEXTJOIN(" ",,FILTERXML("<A><B>"&SUBSTITUTE(TRIM(SUBSTITUTE(z,CHAR(38),"&"))," ","</B><B>")&"</B></A>","//B[translate(.,'1234567890','')=.]")))),
E,IF(INDEX(C,,1)="","",INDEX(B,,4)),
HSTACK(C,D,TAKE(B,,-2),E,{""})),IFERROR(CHOOSECOLS(f,1,2,3,7,7,7,7,5,7,4,6),""))

Thanx Gregb11,you did a splendid work.



.

Another generic solution that should work for each table F:

Please

Replace in a working formula

Formula:

Please Login or Register  to view this content.

with

Formula:

Please Login or Register  to view this content.

See attachment for a sample.

In the formula of Glenn please try:

Please Login or Register  to view this content.

Thanx HansDouwe. Position of col seems to be altered.

Or in your demo data of Post #7, please try:

Please Login or Register  to view this content.

Ok that is good.

I don't understand what you mean with: "I do not like to extract", but you can also try

Please Login or Register  to view this content.

or more generic:

Please Login or Register  to view this content.

Thanx all of u.

Thanks for the feedback.

If there any questions left don't hesistate to ask these questions here.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.