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

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:
42306-WVrich1

After run code:
42307-WVrich2

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

Reply | Quote

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.

Reply | Quote

You can also use the Replace method:

Code:

 Range("Field").Replace ";",  chr(10), xlpart

Reply | Quote