Hello,

I looked all over the internet to find a way to collapse a string of data WITHIN a cell and I got only search results that show how to collapse and expand rows in Excel 2007 (which is NOT what I'm looking for).

I have a problem when importing a Bill Of Material from MS Excel 2007 into Any system. The issue is the cell data gets truncated where the reference designators are when there are hundreds of them

For example
R1, R2, R3, R6, R8, R9...R309, R310...

Import into any system only sees: R1, R2, R3, R6, R8, R9...R30

Bill Of Material initially saved as .XLS. I tried importing this file and didn't work as shown ex. above. After running MACRO (which MACRO does not work in Excel 2007 .XLSX file, only in .XLS), I saved file in .XLSX and then tried importing and same error. Then I tried copy from XLS. then paste as values in .XLSX and still same error.

Finally, I have an idea but don't know how to do this. I want to collapse reference designators into ranges WITHIN CELL like this (to save character space in cell):

R1, R2, R3, R6, R8, R9...R309, R310... --> R1-R3, R6, R9...R309-R310, etc.

How do I do this please?

Hi and welcome
As you didn't provide a sheet containing data and macro, it will be difficult to help.
Just be aware that a macro wbook is usually xlsm ( not xlsx format) starting in 2007

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here's file. Please see cells D9 and D12 which have many references that need to collapse to ranges.

Thanks,

Frank

Attached Files

BILL OF MATERIAL TEMPLATES - NEW PCBA (SMT-TH-MECH) v5.xls (55.0 KB, 10 views)

For some code to concatenate sequential number ranges, see: https://www.msofficeforums.com/word-...html#post68204
In your case, you'd need to supplement that with code to remove the preceding letters before running the function, then restoring them to each 'group' after it's generated the parsed list.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]