Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Morgantown, WV
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to automate process of replacing semi-colon within cell with line break (Alt-Enter)

    Hi folks,

    I have a spreadsheet (in Excel 2007), which I use for formatting the output from a database. In 3 of the columns, I have concatenated two database fields, with the data separated by a semi-colon. To display these columns in the least amount of horizontal space, I replace the semi-colon with a line break, Alt-Enter.

    So far, I have not been able to figure out a way to automate that process, and have been forced to do it manually.

    Search and Replace within Excel will find the semi-colon, but will not accept the Alt-Enter as a valid entry in the Replace field. So I copied several rows including the column(s) of interest to WordPerfect, and was easily able to replace the semi-colon with a hard return, and keeping the now two lines within the same cell.

    However, when I copied that data back to a blank spreadsheet, it stuck every one of those new lines in a separate row. It wouldn't keep the cell data together. My embedded hard return was treated as the end of line, so everything after that was put in the next row.

    I had thought about inserting the code for the Alt-Enter in the database output, but was not able to find the appropriate ASCII code for that function.

    I also tried to use the record macro function to record the steps, but it didn't like the Ctrl-F function.

    I have attached a sample of the data, with examples of both the final result and the starting data.

    TIA for any assistance.

    Rich
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,326
    Thanks
    3
    Thanked 215 Times in 198 Posts
    Ctrl+H,
    Enter ; in the Find box
    Click in the Replace box, hold Alt and type 0010 on the number keypad, then press Replace All. (the ASCII code is 10)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,359
    Thanks
    162
    Thanked 623 Times in 591 Posts
    Hi

    In your file..
    select column [D], then
    press [Ctrl][H] for find and replace
    enter the semi-colon ; for 'find what'
    in the 'replace with' box, enter [Ctrl][Shift][J]
    ..select [Replace All..]
    ..then, with column [D] still selected, in the top-panel Ribbon use Home>Format>Autofit Row Height

    zeddy

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,884
    Thanks
    147
    Thanked 734 Times in 666 Posts
    WVrich,

    A different approach. This could also be easily done with a macro. For this example, the cells to be modified are in a named range called "Field". Just click the run code button. Good for large ranges containing many cells.

    HTH,
    Maud

    Before run code:
    WVrich1.png

    After run code:
    WVrich2.png

    Place in a standard module:
    Code:
    Public Sub Ditch_SemiColon()
    Dim cell As Range, rng As Range
    Set rng = Range("Field")
    For Each cell In rng
        cell = Trim(Replace(cell, ";", Chr(10), 1, vbTextCompare))
    Next cell
    End Sub
    Attached Files Attached Files

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,326
    Thanks
    3
    Thanked 215 Times in 198 Posts
    You can also use the Replace method:
    Code:
     Range("Field").Replace ";",  chr(10), xlpart

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Morgantown, WV
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Ctrl+H,
    Enter ; in the Find box
    Click in the Replace box, hold Alt and type 0010 on the number keypad, then press Replace All. (the ASCII code is 10)
    Thanks Rory, that worked. With the ASCII code, I'll go back to the database export, to see if I can embed that, and not have to change it in the spreadsheet.

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Morgantown, WV
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [QUOTE=Maudibe;1026137]WVrich,

    A different approach. This could also be easily done with a macro. For this example, the cells to be modified are in a named range called "Field". Just click the run code button. Good for large ranges containing many cells.

    Thanks Maudibe,

    Is that handle a Dune reference?

    The advice from Rory and Zeddy did the trick. As time allows, I'll try your macro. I need to learn more about Excel macros anyway.

Posting Permissions

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