Formula in textbox linked to cell only shows part of the text

I have some textboxes that contain formula linking them to a cell value. They only show part of the text in the cell and I can't figure out why. I have attached a sample sheet in case anyone has any ideas.

Excel will truncate a passed string at 255 characters, so that is why your message is being cut off.

More than likely you can't have more than 256 characters.
Use this
=MID(B13,1,256)
-you will see it stops at the same place.

Originally Posted by davesexcel

More than likely you can't have more than 256 characters.
Use this
=MID(B13,1,256)
-you will see it stops at the same place.

Tried this but got an error message stating 'Text you entered is not a valid reference or defined name'

im trying to find a work around by using

=LEFT(B13,255)&MID(B13,255,255)

but it appears that a textbox will now allow that formula.

you could probably enter it in using a VBA macro though.

you may need to result to a VBA macro to get past the 255 character limit, i could not find any other way around it.

Any ideas about what the macro would look like?

somethign like this:

Sub textboxtext()
ActiveSheet.Shapes("TextBox 1").Select
Selection.Characters.Text = Left(Range("B13").Value, 255) & Mid(Range("B13").Value, 255, 255)
End Sub

Excellent. Thank you very much