+ Reply to Thread
Results 1 to 6 of 6

Concatenating cells where one contains Pipe delimited data

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    4

    Concatenating cells where one contains Pipe delimited data

    Hi all,
    I have a sheet of data with the following:
    A1 Text B1 Text 1|Text 2|Text 3|Text 4|
    A2 Text B2 Text 5|Text 6|Text 7|Text 8|

    I'd like to create a new sheet that would (a) Convert Pipe Delimited Text into rows and (b) append/concatenate A1 Text to each B1 Text Element. So, final list would look like:

    A1 Text;B1Text1
    A1 Text;B1Text2
    A1 Text;B1Text3
    A1 Text;B1Text4
    A2 Text;B2Text5
    A2 Text:B2Text6
    A2 Text;B2Text7...etc

    I'm trying to create a file for social graph analysis and these are "nodes" in the network. Any help would be GREATLY appreciated. I have a script now that successfully converts Pipe delimited data from B1 in to multiple rows, but it does not append/concatenate data from A1. Here is that script I'm using (if it helps)...



    Sub Macro1()
    Dim fromCol As String
    Dim toCol As String
    Dim fromRow As String
    Dim toRow As String
    Dim inVal As String
    Dim outVal As String
    Dim commaPos As Integer

    ' Copy from column A to column B.'
    fromCol = "A"
    toCol = "B"
    fromRow = "1"
    toRow = "1"

    ' Go until no more entries in column A.'
    inVal = Range(fromCol + fromRow).Value
    While inVal <> ""

    ' Go until all sub-entries used up.'
    While inVal <> ""
    Range(fromCol + fromRow).Select

    ' Extract each subentry.'
    commaPos = InStr(1, inVal, "|")
    While commaPos <> 0

    ' and write to output column.'
    outVal = Left(inVal, commaPos - 1)
    Range(toCol + toRow).Select
    Range(toCol + toRow).Value = outVal
    toRow = Mid(Str(Val(toRow) + 1), 2)

    ' Remove that sub-entry.'
    inVal = Mid(inVal, commaPos + 1)
    While Left(inVal, 1) = " "
    inVal = Mid(inVal, 2)
    Wend
    commaPos = InStr(1, inVal, "|")
    Wend

    ' Get last sub-entry (or full entry if no commas).'
    Range(toCol + toRow).Select
    Range(toCol + toRow).Value = inVal
    toRow = Mid(Str(Val(toRow) + 1), 2)
    inVal = ""
    Wend

    ' Advance to next source row.'
    fromRow = Mid(Str(Val(fromRow) + 1), 2)
    Range(fromCol + fromRow).Select
    inVal = Range(fromCol + fromRow).Value
    Wend
    End Sub

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Concatenating cells where one contains Pipe delimited data

    Give this a try. Also need to place any code that you post in code tags

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    10-01-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Concatenating cells where one contains Pipe delimited data

    Awesome! Works like a champion! Thanks.

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Concatenating cells where one contains Pipe delimited data

    Mike--
    Sorry to ask the proverbial "follow on ?" but...

    There are some cells in my data, either A1 or B1 in example above that are blank, and this seems to stop the script. Is there any way to have the script ignore that row (if either are blank) and continue on?

    Thanks,
    Alan

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517
    Doing this from my phone so hopefully its right

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-01-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Concatenating cells where one contains Pipe delimited data

    You are the MAN! Many, many thanks...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1