Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-29-2017, 09:23 AM
fdirosa fdirosa is offline Putting data in ranges within cell MS Excel 2007 Windows 7 64bit Putting data in ranges within cell MS Excel 2007 Office 2007
Novice
Putting data in ranges within cell MS Excel 2007
 
Join Date: Nov 2017
Posts: 2
fdirosa is on a distinguished road
Default Putting data in ranges within cell MS Excel 2007

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?
Reply With Quote
  #2  
Old 11-30-2017, 01:24 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Putting data in ranges within cell MS Excel 2007 Windows 7 64bit Putting data in ranges within cell MS Excel 2007 Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #3  
Old 12-04-2017, 01:52 PM
fdirosa fdirosa is offline Putting data in ranges within cell MS Excel 2007 Windows 7 64bit Putting data in ranges within cell MS Excel 2007 Office 2007
Novice
Putting data in ranges within cell MS Excel 2007
 
Join Date: Nov 2017
Posts: 2
fdirosa is on a distinguished road
Default

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

Thanks,

Frank
Reply With Quote
  #4  
Old 12-15-2017, 03:33 PM
macropod's Avatar
macropod macropod is offline Putting data in ranges within cell MS Excel 2007 Windows 7 64bit Putting data in ranges within cell MS Excel 2007 Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,346
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can Excel find Duplicate entries when only part of the cell's data is a duplicate of another cell? jsisley Excel 1 07-21-2017 09:20 AM
Putting data in ranges within cell MS Excel 2007 Populating ranking depending upon ranges of previous cell's data sryder Excel 5 04-07-2016 03:37 PM
How to replicate, consolidate and count cell data in excel for data consoldation. Loran Excel 7 06-05-2014 01:07 AM
Putting data in ranges within cell MS Excel 2007 Excel 2007, highlight entire row based on data found in one cell MSofficeBLUE Excel 2 10-15-2013 09:51 PM
Putting data in ranges within cell MS Excel 2007 putting image file into a cell of a table hklein Word VBA 1 04-19-2013 03:34 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft