Split contents of cell into multiple cells.

[jbarrick007]

I have an excel page that i am using linking info from a cell into a text box. But linking cells to text boxes has a limit of 255 characters. So I would like to understand how to use the LEN function to break the contents of one cell into 4. Then I create 4 text boxes instead of one so that I can still have all the data linked to the cell results. This might be a bit crude but it works with the way I have the data being manipulated on the other cells but wanting to have a nice visual tab to see the results.

Column 1 is what I currently have. Column 2-4 show that I need it broken down in to a max of 255 characters, below is just a sample, not the exact way each cell needs broken down. However, the issue is using a simple LEN formula would probably leave me with a bunch of broken in half bullet points.

Is there a way around this, or will this require just manual work?

#3.06 | PROJECT REQUISITIONS - Duration: Tue 12/7/21 (7 wks) • Coordinate the installation of temporary power, internet, and water (if needed) • Finalize Site Safety And Security Plan with Oversight and post on project site. • Using the approved Procurement Plan as a guide, ensure project purchase requisitions for materials, services, and rental equipment have been submitted - • Submit project purchase requisitions for outside contractors • Coordinate with zone buyer to finalize outside contracts • Coordinate with zone buyer to complete accounts with local vendors • Begin meeting with Budget Review Board with financial data	#3.06 | PROJECT REQUISITIONS - Duration: Tue 12/7/21 (7 wks) • Coordinate the installation of temporary power, internet, and water (if needed) • Finalize Site Safety And Security Plan with Oversight and post on project site.	• Using the approved Procurement Plan as a guide, ensure project purchase requisitions for materials, services, and rental equipment have been submitted - • Submit project purchase requisitions for outside contractors • Coordinate with zone buyer to finalize outside contracts	• Coordinate with zone buyer to complete accounts with local vendors • Begin meeting with Budget Review Board with financial data
#9.15 | CONSTRUCTION CLOSEOUT - Duration: 5/3/22 (1 wk) • Review and update all safety documentation as needed • Call off rental services that are no longer needed • Conduct Phase 9 Purchasing Closeout Meeting • Collect all remaining user documentation for facility • Complete remaining system commissioning • Ensure progressive construction closeout tasks are completed • Review financial data Budget Review Board • Schedule final walk-through with Property Owners. • Ensure building is ready for temporary Certificate of Occupancy (if applicable) • Close all open permits. Pull occupancy permit (if applicable) • Verify all quality control related documentation has been uploaded to Project Site • Ensure all Contractor Reviews are completed	#9.15 | CONSTRUCTION CLOSEOUT - Duration: 5/3/22 (1 wk) • Review and update all safety documentation as needed • Call off rental services that are no longer needed • Conduct Phase 9 Purchasing Closeout Meeting • Collect all remaining user documentation for facility	• Complete remaining system commissioning • Ensure progressive construction closeout tasks are completed • Review financial data Budget Review Board • Schedule final walk-through with Property Owners. • Ensure building is ready for temporary Certificate of Occupancy (if applicable)	• Close all open permits. Pull occupancy permit (if applicable) • Verify all quality control related documentation has been uploaded to Project Site • Ensure all Contractor Reviews are completed

 

[RayFrye]

This code will get you started, a Cell (CellToSpit) is split by a specific count (SplitCount) and the output is slammed in a column (SaveSplitColumn).

You can split a cell (ex C1), by any number (ex: 200), and cram the data into a column (ex Dx).

Modify as necessary

Sub start()
    Call ProcessCellChatacters("C1", 200, "D")
End Sub

Sub ProcessCellChatacters(CellToSpit, SplitCount, SaveSplitColumn)

    RowToStore = 1
    HowManyTotalCharactersToSplit = Len(Range(CellToSpit).Value)
    
    CharactersData = Range(CellToSpit).Value
    
    HowManySplits = CInt(HowManyTotalCharactersToSplit / SplitCount) + 1
     
    On Error Resume Next
    For x = RowToStore To HowManySplits
    
        Range(SaveSplitColumn & x).Value = "'" & Left(CharactersData, SplitCount)
        CharactersData = Right(CharactersData, Len(CharactersData) - SplitCount)
        
    Next
    On Error GoTo 0

End Sub

 

[DanteAmor]

In the following example I have an ActiveX Control textbox and it is linked to cell A3. The text in cell A3 is 631 characters.

 

[jbarrick007]

In the following example I have an ActiveX Control textbox and it is linked to cell A3. The text in cell A3 is 631 characters.

View attachment 19968

Thank you. I wasn't familiar with that working under different rules then a plain text box. Ill have to look to see if my formatting will still work. but thank you.

 

[sandy666]

maybe something like this?
you can use Wrap Text if it's really necessary

Column1.1	Column1.2	Column1.3	Column1.4	Column1.5	Column1.6	Column1.7	Column1.8	Column1.9
#3.06 | PROJECT REQUISITIONS	- Duration: Tue 12/7/21 (7 wks)	• Coordinate the installation of temporary power, internet, and water (if needed)	• Finalize Site Safety And Security Plan with Oversight and post on project site.	• Using the approved Procurement Plan as a guide, ensure project purchase requisitions for materials, services, and rental equipment have been submitted -	• Submit project purchase requisitions for outside contractors	• Coordinate with zone buyer to finalize outside contracts	• Coordinate with zone buyer to complete accounts with local vendors	• Begin meeting with Budget Review Board with financial data
#9.15 | CONSTRUCTION CLOSEOUT	- Duration: 5/3/22 (1 wk)	• Review and update all safety documentation as needed	• Call off rental services that are no longer needed	• Conduct Phase 9 Purchasing Closeout Meeting	• Collect all remaining user documentation for facility	• Complete remaining system commissioning	• Ensure progressive construction closeout tasks are completed	• Review financial data Budget Review Board

 

[sandy666]

or like this

Column1
#3.06 | PROJECT REQUISITIONS
- Duration: Tue 12/7/21 (7 wks)
• Coordinate the installation of temporary power, internet, and water (if needed)
• Finalize Site Safety And Security Plan with Oversight and post on project site.
• Using the approved Procurement Plan as a guide, ensure project purchase requisitions for materials, services, and rental equipment have been submitted -
• Submit project purchase requisitions for outside contractors
• Coordinate with zone buyer to finalize outside contracts
• Coordinate with zone buyer to complete accounts with local vendors
• Begin meeting with Budget Review Board with financial data
#9.15 | CONSTRUCTION CLOSEOUT
- Duration: 5/3/22 (1 wk)
• Review and update all safety documentation as needed
• Call off rental services that are no longer needed
• Conduct Phase 9 Purchasing Closeout Meeting
• Collect all remaining user documentation for facility
• Complete remaining system commissioning
• Ensure progressive construction closeout tasks are completed
• Review financial data Budget Review Board
• Schedule final walk-through with Property Owners.
• Ensure building is ready for temporary Certificate of Occupancy (if applicable)
• Close all open permits. Pull occupancy permit (if applicable)
• Verify all quality control related documentation has been uploaded to Project Site
• Ensure all Contractor Reviews are completed