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,280
    Thanks
    3
    Thanked 191 Times in 177 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
    2,829
    Thanks
    136
    Thanked 483 Times in 460 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,639
    Thanks
    115
    Thanked 650 Times in 592 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,280
    Thanks
    3
    Thanked 191 Times in 177 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
  •