METHOD #1:
The following will strip the "Alt-Enter" (enter this as you would normally between the quotes
=REPLACE(G7,SEARCH("
",G7),1," ")
or
=REPLACE(G7,SEARCH(CHAR(10),G7),1," ")
However, if there is no Alt-enter, then search will return N/A so you need to add If statement to accomodate this. And you can't loop, or count the number, so you need to hard code enough repetitions to cover the likely events.
METHOD #2
Easier to use a loop in a VBA macro to clean it. (two subs below are similar to what you need)
METHOD #3 - tentative
If the user enters in one cell, then you could use the trim function to display in another cell. This will strip extra spaces.
DisplayCell = trim(entryCell)
It is a little bizarre though. When
EntryCell = 1alt-enter2
Trim() = 12
but when paste special> value it still displays as 12, but then if F2 to edit the pasted location and then return to save it, the alt-enter is still there and displays as 2 lines!
MORE ON METHOD #2:
You can use VBA to strip any Alt-Enter characters, trim and then truncate at 200 characters. This macro could be run on the Worksheet_change event.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A10")) Is Nothing Then
trim cell "A10"
Else
do something else
End If
End Sub
Function CountElements(Txt, Separator) As String
' Counts the number of elements that are separated by a specified separator character in a text string
Dim Txt1, LastCharacter As String
Dim ElementCount As Integer, i As Integer
' Initialize
ElementCount = 0
Txt1 = Txt
' Check that the cell is not blank
If Txt1 = "" Then
CountElements = ""
Exit Function
End If
' Check if there is at least one element
If Txt1 = Separator Then
CountElements = 0
Exit Function
End If
' If the last character is not a separator, then add a separator to the end of the string
LastCharacter = Right(Txt1, 1)
If LastCharacter <> Separator Then Txt1 = Txt1 & Separator
' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
End If
Next i
CountElements = ElementCount
End Function
Function ExtractElement(Txt, n, Separator) As String
' Returns the nth element of a text string, where the elements
' are separated by a specified separator character
Dim Txt1 As String, TempElement As String
Dim ElementCount As Integer, i As Integer
Txt1 = Txt
' If space separator, remove excess spaces
If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then Txt1 = Txt1 & Separator
' Initialize
ElementCount = 0
TempElement = ""
' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
If ElementCount = n Then
' Found it, so exit
ExtractElement = TempElement
Exit Function
Else
TempElement = ""
End If
Else
TempElement = TempElement & Mid(Txt1, i, 1)
End If
Next i
ExtractElement = ""
End Function