Is there an easy way to convert text in a Text Box to text in a cell ?

[scopitch]

I have spreadsheets where much of the text has been created by adding a text box to the worksheet. To use the spreadsheet with some other software, the text has to appear in regular Excel cells. Apart from spending hours copying and pasting, does anyone know of a way to make this change ?

Thanks

 

[GopherUK]

Do you mean all the text appears in a single cell as one string whereas you want it to be split across cells?

Can you post an example please?

 

[scopitch]

Thanks for quick reply. Haven't posted here before - can I attach a spreadsheet ? I'm not sure how best to show it.

If you imagine a text box Inserted into a worksheet. The text is within the box, and the box sits on top of the Excel cells.

I need that text to appear as if it had been typed into Excel directly - it can all be in a single cell, doesn't have to split.

Does that make sense ?

 

[GopherUK]

It just sounds like a display problem. Is it a case that the text is wrapping in the cell, giving the cell a larger height?

 

[pgc01]

To extract the text from textboxes into cells you need vba. Loop through the textboxes and write the text into cells.

 

[Domenic]

The following macro loops through each TextBox and writes the text in Column A, starting at A1. It also deletes each of these TextBoxes. If you don't want to delete them, remove the appropriate line from the code.

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] TxtBox [color=darkblue]As[/color] TextBox

    r = 1 [color=green]'start in Row 1[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] TxtBox [color=darkblue]In[/color] ActiveSheet.TextBoxes
        Cells(r, "A").Value = TxtBox.Text
        TxtBox.Delete
        r = r + 1
    [color=darkblue]Next[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[PaddyD]

...being careful to check that (length of text in textbox) <= (maximum number of characters that can be stored in a single cell (32,767)). Otherwise you'll have to split single trextbox contents over multiple cells.

 

[pgc01]

... and that they are excel textboxes and not activeX controls.

 

[scopitch]

Thanks to all posters. The macro has worked on a small text sheet, so I will have a go at it in the new year on the more complex sheet. The help is greatly appreciated.

 

[Domenic]

That's great. As Paddy has already pointed out, though, a cell can only hold 32,767 characters. Therefore, if a TextBox can hold more than this limit, the code will need to be amended.