Microsoft Excel - How to display full text from cell into formula bar

Asked By Charles Bostic on 15-Feb-13 12:05 PM
I believe I have a space issue in Excel 2010 that prevents the full text displayed in certain cells does not show up in full in the formula bar. For an example, text located in cell AC19 displays as "Virtual Event Platform Login (LOGIN_VEP)" in the formula bar unless I delete the space after "(LOGIN_VEP)" then it shows the full text "Virtual Event Platform Login (LOGIN_VEP)Site ID: SAPPHIRENOW". I need a formula or solution that I can drag throughout th spreadsheet to fix simliar issues. 
Karsten Awizio replied to Charles Bostic on 18-Feb-13 09:13 AM
Hi Charles,

this formula removes up to one line break when reading the string from cell AC19:
=IFERROR(LEFT(AC19,FIND(CHAR(10),AC19)-1) & " " & RIGHT(AC19,LEN(AC19)-FIND(CHAR(10),AC19)), AC19)

CHAR(10) is the character used for line break within a string.
The IFERROR statement is used to bypass strings without a line break.

If you could have more than one line break per cell in some cases, I suggest to use a three spets approach instead of just one formula because of the large number of cases to be rspected: First, determine the different CHAR(10) positions, then cut the string on all positions and finally combine these parts together again.

Good luck,
Karsten