Getting error msg using delimited text string via formula

Spudz

New Member
Joined
Apr 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I found an old thread titled "Separate out comma delimited text string via formula", which works for my particular application.
It's using formula, =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(B$1-1)*LEN($A2)+1,LEN($A2)))

However, it will only work going out to column CD; I need it to go out much further.
- using Excel 2016
- Windows platform
- using with large text string similar to the following: C2, C3, C4, C5, C7, C8, C9, C10, etc

Any help would be greatly appreciated!

Thanks in advance,
Spudz
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Spudz

New Member
Joined
Apr 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I found an old thread titled "Separate out comma delimited text string via formula", which works for my particular application.
It's using formula, =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(B$1-1)*LEN($A2)+1,LEN($A2)))

However, it will only work going out to column CD; I need it to go out much further.
- using Excel 2016
- Windows platform
- using with large text string similar to the following: C2, C3, C4, C5, C7, C8, C9, C10, etc

Any help would be greatly appreciated!

Thanks in advance,
Spudz
Forgot to mention; i get this error message: #VALUE! in cells after column CD
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,036
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What is the length of text in cell A2?

What is in the cells B1, B2, etc.?
 

Spudz

New Member
Joined
Apr 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
What is the length of text in cell A2?

What is in the cells B1, B2, etc.?
Hi Rick,
- The length could be max of 300 "C's", in this case; so for example it would be C1 thru C300, all separated by a comma.
- Cell B1 is actually part of the header, which is the number 1; C1 is number 2, D1 is number 3, etc.
- Cell B2 would be the first cell to use this formula.

I uploaded a .jpg picture sample of what we're doing (used at an Electronic Manufacturing facility); didn't have time to figure out the mini-sheet (meeting).
- the green shaded cells indicate this part is "Unique"; not shaded means there's a duplicate part in list; this needs to go to the right for a max of 300 columns (not counting column A.


Hope this made sense; thanks for your help!
Spudz
 

Attachments

  • Mr Excel Sample file_4-7-21_Spudz.jpg
    Mr Excel Sample file_4-7-21_Spudz.jpg
    225.4 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
11234567891011121314
2C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,C59,C60,C61,C62,C63,C64,C65,C66,C67,C68,C69,C70,C71,C72,C73,C74,C75,C76,C77,C78,C79,C80,C81,C82,C83,C84,C85,C86,C87,C88,C89,C90,C91,C92,C93,C94,C95,C96,C97,C98,C99,C100,C101,C102,C103,C104,C105,C106,C107,C108,C109,C110,C111,C112,C113,C114,C115,C116,C117,C118,C119,C120,C121,C122,C123,C124,C125,C126,C127,C128,C129,C130,C131,C132,C133,C134,C135,C136,C137,C138,C139,C140,C141,C142,C143,C144,C145,C146,C147,C148,C149,C150,C151,C152,C153,C154,C155,C156,C157,C158,C159,C160,C161,C162,C163,C164,C165,C166,C167,C168,C169,C170,C171,C172,C173,C174,C175,C176,C177,C178,C179,C180,C181,C182,C183,C184,C185,C186,C187,C188,C189,C190,C191,C192,C193,C194,C195,C196,C197,C198,C199,C200,C201,C202,C203,C204,C205,C206,C207,C208,C209,C210,C211,C212,C213,C214,C215,C216,C217,C218,C219,C220,C221,C222,C223,C224,C225,C226,C227,C228,C229,C230,C231,C232,C233,C234,C235,C236,C237,C238,C239,C240,C241,C242,C243,C244,C245,C246,C247,C248,C249,C250,C251,C252,C253,C254,C255,C256,C257,C258,C259,C260,C261,C262,C263,C264,C265,C266,C267,C268,C269,C270,C271,C272,C273,C274,C275,C276,C277,C278,C279,C280,C281,C282,C283,C284,C285,C286,C287,C288,C289,C290,C291,C292,C293,C294,C295,C296,C297,C298,C299,C300C1C2C3C4C5C6C7C8C9C10C11C12C13C14
3D1,D2,D3,D4,D5,D6,D7,D8,D9,D10D1D2D3D4D5D6D7D8D9D10    
4R5R5             
Main
Cell Formulas
RangeFormula
B2:B4,C3:O4B2=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE($A2,",","</m><m>")&"</m></k>","//m["&COLUMNS($B2:B2)&"]"),"")
C2:O2C2=FILTERXML("<k><m>"&SUBSTITUTE($A2,",","</m><m>")&"</m></k>","//m["&COLUMNS($B2:C2)&"]")
 
Solution

Spudz

New Member
Joined
Apr 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNO
11234567891011121314
2C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,C59,C60,C61,C62,C63,C64,C65,C66,C67,C68,C69,C70,C71,C72,C73,C74,C75,C76,C77,C78,C79,C80,C81,C82,C83,C84,C85,C86,C87,C88,C89,C90,C91,C92,C93,C94,C95,C96,C97,C98,C99,C100,C101,C102,C103,C104,C105,C106,C107,C108,C109,C110,C111,C112,C113,C114,C115,C116,C117,C118,C119,C120,C121,C122,C123,C124,C125,C126,C127,C128,C129,C130,C131,C132,C133,C134,C135,C136,C137,C138,C139,C140,C141,C142,C143,C144,C145,C146,C147,C148,C149,C150,C151,C152,C153,C154,C155,C156,C157,C158,C159,C160,C161,C162,C163,C164,C165,C166,C167,C168,C169,C170,C171,C172,C173,C174,C175,C176,C177,C178,C179,C180,C181,C182,C183,C184,C185,C186,C187,C188,C189,C190,C191,C192,C193,C194,C195,C196,C197,C198,C199,C200,C201,C202,C203,C204,C205,C206,C207,C208,C209,C210,C211,C212,C213,C214,C215,C216,C217,C218,C219,C220,C221,C222,C223,C224,C225,C226,C227,C228,C229,C230,C231,C232,C233,C234,C235,C236,C237,C238,C239,C240,C241,C242,C243,C244,C245,C246,C247,C248,C249,C250,C251,C252,C253,C254,C255,C256,C257,C258,C259,C260,C261,C262,C263,C264,C265,C266,C267,C268,C269,C270,C271,C272,C273,C274,C275,C276,C277,C278,C279,C280,C281,C282,C283,C284,C285,C286,C287,C288,C289,C290,C291,C292,C293,C294,C295,C296,C297,C298,C299,C300C1C2C3C4C5C6C7C8C9C10C11C12C13C14
3D1,D2,D3,D4,D5,D6,D7,D8,D9,D10D1D2D3D4D5D6D7D8D9D10    
4R5R5             
Main
Cell Formulas
RangeFormula
B2:B4,C3:O4B2=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE($A2,",","</m><m>")&"</m></k>","//m["&COLUMNS($B2:B2)&"]"),"")
C2:O2C2=FILTERXML("<k><m>"&SUBSTITUTE($A2,",","</m><m>")&"</m></k>","//m["&COLUMNS($B2:C2)&"]")


Yes - Thank you for your help! This seems to have done the trick!
I can now take the columns out to max of 300; was limited before.

thanks again!
Spudz
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,911
Members
418,250
Latest member
Jebacmakro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top