Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Reston, Virginia, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search for Alt-Enter (97)

    Excel allows inserting a hard return into a cell containing text with the shortcut keys of Alt-Enter. Now I am looking for an easy way to do a search on the Alt-Enter character, and replace it with a couple of space characters.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    You could use the Substitute() function:

    =SUBSTITUTE(A1,CHAR(10)," ")

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    This code will replace Alt-Enter with a single space for every cell in the current selection.

    <pre>Sub ReplaceAltEnter()

    Dim C As Range, Pos As Integer, Ltr As String, NewStr As String, _
    HowMany As Long, StartAt

    Application.ScreenUpdating = False ' Makes things faster

    HowMany = 0
    StartAt = Now()

    For Each C In Selection.Cells
    NewStr = "" ' Start with empty replacement string
    If InStr(1, C.Text, Chr(10)) Then ' There is at least one Alt-Enter; ...
    HowMany = HowMany + 1
    For Pos = 1 To Len(C.Text) ' ... find all, replace with space
    Ltr = Mid(C.Text, Pos, 1) ' One character at a time
    If Ltr = Chr(10) Then
    NewStr = NewStr & " " ' Add a space to replacement string
    Else
    NewStr = NewStr & Ltr ' Add current character to replacement string
    End If
    Next Pos
    C.Value = NewStr ' Put replacement string into cell
    End If
    Next C

    Application.ScreenUpdating = True

    ' Report statistics of the operation
    MsgBox HowMany & " occurences were found and replaced." & vbCrLf & _
    "It took " & Format(Now() - StartAt, "HH:MM:SS") & " (HH:MM:SS)"

    End Sub
    </pre>


  4. #4
    Lounger
    Join Date
    Dec 2001
    Location
    Reston, Virginia, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    Thanks all for your help. Turns out the cells.find command in Excel visual basic allows entry of non-printing characters. This find/replace appeared faster than doing instr on each cell.

    I finally used the below code to replace a given unicode value with a given string value.

    Public Sub SearchReplaceSpecChar()
    ' Replace all chars with entered Unicode value with an ASCII pattern.

    Dim strg As String, newStr As String
    Dim i As Long, lastI As Long
    Dim uCh As Long
    Dim repStr As String

    strg = InputBox("Enter decimal, &Ooctal, or &Hhex value of char to find: ")
    repStr = InputBox("Enter replacement string: ")
    If strg = "" Then
    MsgBox ("No value entered -- exiting!")
    Exit Sub
    Else
    uCh = Val(strg)
    If (uCh < 1) And (uCh > -1) Then
    MsgBox ("Invalid value entered -- exiting!")
    Exit Sub
    End If
    End If

    On Error GoTo Error_Handler

    Cells.Find(What:=ChrW(uCh), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate

    While ActiveCell.Replace(What:=ChrW(uCh), Replacement:=repStr, LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False)
    Cells.Find(What:=ChrW(uCh), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate

    Wend

    Exit Sub
    Error_Handler:
    ' Handle replacements when cell text is too long!
    If Err.Number = 1004 Then
    strg = ActiveCell.Value
    newStr = ""
    lastI = 1
    i = InStr(1, strg, ChrW(uCh), vbTextCompare)
    While i > 0
    strg = Mid(strg, 1, i - 1) & repStr & Mid(strg, i + 1)
    i = InStr(1, strg, ChrW(uCh), vbTextCompare)
    Wend
    ActiveCell.Value = strg

    Resume Next
    ' Quit when the special char is not found
    ElseIf Err.Number = 91 Then
    MsgBox "Routine Complete!!"
    Else

    Err.Raise Err.Number
    End If


    End Sub

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    In the Search box type (while holding the Alt key) 010 on your numeric keypad. You'll see nothing appear, but don't worry. Now tab to the replace box and type your replacement text.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Dec 2001
    Location
    Reston, Virginia, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    That's it!!! i had forgotten about how windows allows entry of any character using the keypad. Plus I can use the same approach to convert a string back to the Alt-Enter character. This resolves the problem I had in moving data between Word and Excel in data winding up in the wrong cell due to the Alt-Enter character/paragraph mark.
    Thanks.

  7. #7
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    Hello Jan Karel,

    How are the numbers found that associate with the keys? Rather, how was it found out that 010 is the code for Alt-Enter?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for Alt-Enter (97)

    Well, I just knew that one.

    For the rest : these are the standard ASCII character codes. Char(13) is a carriage return, char(34) the " etc.

    You can show these characters in XL (of course some of the above will not <g>) by entering this formula on row 1 somewhere and copying down:

    =CHAR(ROW())
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •