Split contents of cell into multiple cells.

jbarrick007

New Member
Joined
Jul 9, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
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

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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.

1596940221211.png
 

jbarrick007

New Member
Joined
Jul 9, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe something like this?
you can use Wrap Text if it's really necessary

Column1.1Column1.2Column1.3Column1.4Column1.5Column1.6Column1.7Column1.8Column1.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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,896
Messages
5,627,512
Members
416,250
Latest member
darius_rebelo

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