+ Reply to Thread
Results 1 to 8 of 8

Split merged cells from Infopath - Sharepoint

Hybrid View

  1. 06-09-2014, 05:39 PM #1
    fer22
    fer22 is offline
    Registered User
    Join Date
    06-09-2014
    Posts
    3

    Arrow Split merged cells from Infopath - Sharepoint

    Hi everybody!

    I'm new to this forum and I was looking for some help using macros.
    My scenario is like this: I have an Infopath form with some repeating tables in it, for those who don't know, the fields from a repeating table appear all merged in one same cell. So when I export this form to Excel I got all my information from diferrent rows all merged in one. They have a dilimeter between them which is an Indentation.

    I've attached an example so you could see the .xlsx I get from Sharepoint.

    What I'm trying to achieve is split every field into different rows, for example the column "Dia Nro" should have three rows with, 1, 2 and 3 respectively.

    Sorry if I wasn't clear enough but English is not my native language

    Hope somebody can help me, I would really appreciate any help!

    Thanks for reading!

    Have a nice day.

    fer22.


    example.xlsx
    Reply With QuoteRegister To Reply
  2. 06-09-2014, 06:37 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,936

    Re: Split merged cells from Infopath - Sharepoint 

    Option Explicit
Sub Splitter()
    Dim TestRange   As Range, _
        TestCell    As Range, _
        Holder      As Variant, _
        LstCol      As Long

    LstCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Set TestRange = Range("A2", Cells(2, LstCol))
    
    For Each TestCell In TestRange
        If TestCell.Value <> "" Then
            Holder = Split(TestCell.Value, Chr(10))
            Select Case UBound(Holder)
                Case 0
                    TestCell.Offset(2, 0).Value = TestCell.Value
                Case Else
                    TestCell.Offset(2, 0).Resize(rowsize:=UBound(Holder) + 1).Value = Application.Transpose(Holder)
            End Select
        End If 'not blank
    Next TestCell
End Sub
    Attached Files Attached Files
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 06-10-2014, 10:26 AM #3
    fer22
    fer22 is offline
    Registered User
    Join Date
    06-09-2014
    Posts
    3

    Re: Split merged cells from Infopath - Sharepoint

    Thank you very much protonLeah! That's amazing!

    This really safistifies my needs, but I'm gonna ask you something else if you could help me. Is it possible to just order the values in the same table instead of copying the values down there? That would be the perfect solution!

    Anyway thank you for reading and for you help!

    fer22.
    Reply With QuoteRegister To Reply
  4. 06-10-2014, 02:29 PM #4
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,936

    Re: Split merged cells from Infopath - Sharepoint

    Try this one:
    Option Explicit
Sub Splitter()
    Dim TestRange   As Range, _
        TestCell    As Range, _
        Holder      As Variant, _
        LstCol      As Long
    
    ' convert the table to a range
    For Each Holder In ActiveSheet.ListObjects
        If Left(Holder.Name, 6) = "Table_" Then
            Application.Goto Reference:=Holder.Name
            ActiveSheet.ListObjects(Holder.Name).Unlist
        End If
    Next Holder
    
    Range("A2").Hyperlinks(1).Delete
    With Rows("2:2")
        .Borders(xlEdgeBottom).LineStyle = xlNone
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

    LstCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Set TestRange = Range("A2", Cells(2, LstCol))
    
    For Each TestCell In TestRange
        If TestCell.Value <> "" Then
            Holder = Split(TestCell.Value, Chr(10))
            Select Case UBound(Holder)
                Case 0
                    TestCell.Value = TestCell.Value
                Case Else
                    TestCell.Resize(rowsize:=UBound(Holder) + 1).Value = Application.Transpose(Holder)
            End Select
        End If 'not blank
    Next TestCell
End Sub
    Reply With QuoteRegister To Reply
  5. 06-10-2014, 05:07 PM #5
    fer22
    fer22 is offline
    Registered User
    Join Date
    06-09-2014
    Posts
    3

    Re: Split merged cells from Infopath - Sharepoint

    Thank you very much!!! That's what I was looking for!

    Awesome job protonLeah!

    Thanks again!

    Have a nice day!

    fer22.
    Reply With QuoteRegister To Reply
  6. 01-20-2015, 11:39 AM #6
    trueness
    trueness is offline
    Registered User
    Join Date
    01-20-2015
    Location
    Turkey
    MS-Off Ver
    2013
    Posts
    1

    Re: Split merged cells from Infopath - Sharepoint

    Quote Originally Posted by fer22 View Post
    Hi everybody!

    I'm new to this forum and I was looking for some help using macros.
    My scenario is like this: I have an Infopath form with some repeating tables in it, for those who don't know, the fields from a repeating table appear all merged in one same cell. So when I export this form to Excel I got all my information from diferrent rows all merged in one. They have a dilimeter between them which is an Indentation.

    I've attached an example so you could see the .xlsx I get from Sharepoint.

    What I'm trying to achieve is split every field into different rows, for example the column "Dia Nro" should have three rows with, 1, 2 and 3 respectively.

    Sorry if I wasn't clear enough but English is not my native language

    Hope somebody can help me, I would really appreciate any help!

    Thanks for reading!

    Have a nice day.

    fer22.


    Attachment 324131
    Hello fer22,
    I am new at forum and searching a solution to my problem which is similar to yours. The problem I have is when I export repeating table field (which contains Text) to excel it separates items with ;#
    How can i set up a macro for this.
    For those who will reply and deal with this issue, thank you very much.
    Reply With QuoteRegister To Reply
  7. 05-01-2019, 11:06 AM #7
    m25v
    m25v is offline
    Registered User
    Join Date
    05-01-2019
    Location
    US
    MS-Off Ver
    365
    Posts
    1

    Re: Split merged cells from Infopath - Sharepoint

    Hi,

    I know this is reviving a very old thread, however the function shared by "protonLeah" is exactly what I'm looking for with 1 exception. My excel file has multiple rows already. If I use the macro shared, it actually overwrites my existing data. How can it be modified so it pastes the data below any current rows.

    Thanks!
    Reply With QuoteRegister To Reply
  8. 05-05-2019, 09:12 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,480

    Re: Split merged cells from Infopath - Sharepoint

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Get data from split/merged cells
    By garymrowe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2013, 10:53 PM
  2. How to make Macro skip over merged cells [yes, i know merged cells were formed in Hell]
    By mastrellz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2012, 11:53 AM
  3. Excel 2007 : Sorting data that is split with merged cells
    By WaldoBurger in forum Excel General
    Replies: 3
    Last Post: 04-07-2010, 08:02 AM
  4. Split merged cells and distribute contents
    By eugene w in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2009, 08:09 PM
  5. Email, forms, Excel, Infopath, Sharepoint
    By Bettergains in forum Excel General
    Replies: 0
    Last Post: 11-10-2005, 06:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1