Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    unmerging merged cells (Excel 2003)

    I have a set of spread sheets where the person that created them has merged a number of cell to display a large amount of text. The problem is that there are several thousand characters in the merged cell. Since excel will allow something over 32,000 characters in the cell but will not display all of them it is a real mess trying to work with the sheet.
    They have included formatting, bold and underlining, on some of the words in the cell. They also have carriage returns embedded in the text.

    I want to break these merged cells into a series of cells in column A so that I can work with them. No matter what I try I seem to run into trouble and lose some of the characters . I tried using the MID function to break out 100 characters into each of however many cells I needed, but finding where the CR/LF go and the formatting is driving me nuts.

    I would appreciate any suggestions.

    M

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: unmerging merged cells (Excel 2003)

    You can search for CR (vbCR)or LF (vbLF) with INSTR. It will give you the position of the searched string. It returns a zero (0) if not found.

    For the formatting, you will have to loop thru each of the CHARACTERS object and look thru the FONT properties in the original and determine the various formatting and then transfer that formatting to the appropriate cells in the "extracted".

    If you need more details, let us know.

    Steve

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unmerging merged cells (Excel 2003)

    Thanks, I was hoping there was an easy way.

    M

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: unmerging merged cells (Excel 2003)

    The "easiest" coding might be to not worry about the formatting at all:
    Copy the original cell to a new cell (which will keep all the formatting) then decide what to delete (you could take a certain number, eg 100 or use INSTR to look for the linefeed) and just delete the end characters. This will save the formatting of the initial chars
    Then copy the original, delete the start "so many" and delete the end "so many" and the middle would have the formatting
    Continue this looping until it is all broken up.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: unmerging merged cells (Excel 2003)

    Here is some example code to do what (I think) you want.
    Add this to module in VB:
    <pre>Sub BreakMeUp(sGet As String, sPut As String)
    Dim rGet As Range
    Dim rPut As Range
    Dim iOffset As Integer
    Dim iStart As Integer
    Dim iEnd As Integer
    Dim iLen As Integer
    Set rGet = Range(sGet)

    iOffset = 0
    iStart = 1
    iEnd = 0
    iLen = Len(rGet)
    Do While iEnd < iLen
    Set rPut = Range(sPut).Offset(iOffset, 0)
    iOffset = iOffset + 1
    rGet.Copy rPut
    iEnd = InStr(iStart, rGet, vbLf)
    If iEnd = 0 Then iEnd = iLen
    rPut.Characters(iEnd, iLen).Delete
    rPut.Characters(1, iStart - 1).Delete
    iStart = iEnd + 1
    Loop

    Set rGet = Nothing
    Set rPut = Nothing
    End Sub</pre>


    Then you can call this routine, by adding a line in code like:

    <pre>Call BreakMeUp("C1", "A1")</pre>


    Where Cell C1 is the cell you want to "break apart" and A1 is where you want to start
    In A1 will be from the start to the first Line feed
    In A2 from the char after the first LF to the 2nd LF
    In A3 from the char after the 2nd LF to the 3rd LF
    Etc

    The cells will keep the formatting they had in C1

    You could also check for length if desired, but you waould have to give more specifics if you need more specific code.

    Hope this helps,
    Steve

Posting Permissions

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