Error: "The text string you entered is too long. Reduce the number of characters used or cancel this entry.

[mattu1990]

Hi Everyone,

I have a bit of a strange one here in Excel 2010. Basically I have a list of coordinates across from column B to column AWU and I need to concatenate them all into one cell with a space between each. In column AWV I am trying the following formula as an example.

<code>
=ConcatenateRange=(B4304:AWU4304)
</code>

The formula runs on this VBA (which adds a space between the coordinates in each cell.)

<code>
Function ConcatenateRange(rCells As Range)


'This function makes a concatenate range add a space between the contents of each cell
'in a selected range.


Dim vTemp As Variant
Application.Volatile


For Each vTemp In rCells
ConcatenateRange = ConcatenateRange & vTemp & " "
Next vTemp
End Function
</code>

the formula works fine and concatenates the contents. The problem however is when I then try copy and pasting the results somewhere else as "Paste Values"

I get the error as show in the title of this thread. The contents show in the cell but not in the formula bar. Every time that cell is then selected, the error shows.

The total cell contents does not exceed the 32,767 limit. It contains 25,000 or less.

Anyone know why this would happen?

Cheers,

 

[mattu1990]

OK do you think there is a way to solve my problem. Maybe even a VBA script could be used to tell Excel to ignore the first minus, but not actually take it out of the cell.

 

[Andrew Poulsom]

Sorry, no.

 

[mattu1990]

Anyone else have any ideas on this one?

 

[Andrew Poulsom]

Why do you need all that text in a single cell? Try preceding it with a space.

 

[mattu1990]

So simple why didn't I think of that. Cheers Andrew works great.

It's a long and boring story as to why I need them in once cell.

Matt

 

[davidmg1982]

Why do you need all that text in a single cell? Try preceding it with a space.

Thanks Andrew, this fix a current issue I was facing. So simple.