?
Solved

Excel - Undo delimeter text to columns

Posted on 2011-03-18
16
Medium Priority
?
625 Views
Last Modified: 2012-05-11
Hi,

After I have split delimited cells into columns, is there a way to reverse this and merge the split cells into original format?

Thank you
0
Comment
Question by:tahirih
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 35166102
Not sure, use the concatenate formula?

if you have "Fred" in A1 and "Bill" in B1 then =A1&","&B1 will give "Fred,Bill" in C1.
0
 

Author Comment

by:tahirih
ID: 35166150
Ok, let me please offer more information. The separator is ~.

The separation extends to about 15 columns - and after I separate, I modify the data, then I need to place it back in delimited form, with the updated information, and with the ~ in place again.

If I just add separated cells, then the ~ is not inclduded.

Thanks.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35166186
Can you post a sample workbook?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35166256
Try


=A1 & "~" & B1 & "~" & C1 & "~" & D1 & "~" & E1 & "~" & F1 & "~" & G1 & "~" & H1 & "~" & I1 & "~" & J1 & "~" & K1 & "~" & L1 & "~" & M1 & "~" & O1

Open in new window

0
 

Author Comment

by:tahirih
ID: 35166321
Thank you everyone. Ok, I was hoping there was a non-formula, more menu driven method to resolving this.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35166463
It can be done with VBA.
0
 
LVL 5

Expert Comment

by:Kannan K
ID: 35166516
Hi,

You can create the Macro to does this. in macro you can call input box to get the seperator symbol and merge the data and create into new sheet. now your expected output will be ready.

KK,
0
 

Assisted Solution

by:dbadmin78
dbadmin78 earned 500 total points
ID: 35185575
This can be done without programming, but it's done outside Excel.

1. Click Start, then type intl.cpl in the box and hit Enter on your keyboard

2. Click the Additional Settings button at the bottom right corner

3. Approx. the 8th one down says "List Separator."  Delete whatever is in there and then type your delimiter that you want (I think it was ~  )

4. Click Apply/Ok, Apply/Ok

5. Save your Excel file as a CSV and instead of commas, it will now use the "~" as a delimiter instead of the comma.

6. Test it by opening the file from Notepad.
0
 
LVL 1

Assisted Solution

by:martyk
martyk earned 500 total points
ID: 35191755
How about this...

 
Public Sub ColumnsToText()
'assumes first column cells are selected just like text-to-columns

Dim merged As String
Dim delimiter As String

delimiter = "," ' adjust as needed

For Each i In Range(Selection.Cells(1, 1), Selection.Cells(1, 1).EntireRow.End(xlDown))
    Set RowRange = Range(i.Cells(1, 1), i.EntireRow.End(xlToRight))
    merged = RowRange.Cells(1, 1).Value ' seed with first column
    ' append remaining columns
    For j = 2 To RowRange.Count
        merged = merged & delimiter & RowRange.Cells(1, j).Value
    Next j
    RowRange.Clear
    RowRange.Cells(1, 1).Value = merged
Next i

End Sub

Open in new window


Marty
0
 
LVL 5

Expert Comment

by:Kannan K
ID: 35196469
Tahirh,

Try with this below code for your exact reference.

KK,
Sub MergeColumn()

Dim merged As String
Dim delimiter As String
Dim NumRows As Integer
Dim NumCols As Integer

delimiter = InputBox("Enter delimeter Symbol", "Merge Columns")
NumRows = InputBox("Enter Number of Rows:", "Merge Columns")
NumCols = InputBox("Enter Number of Columns:", "Merge Columns")

For Each i In Range(Selection.Cells(1, 1), Selection.Cells(NumRows, NumCols))
    Set RowRange = Range(i.Cells(1, 1), i.Cells(NumRows, NumCols))
    merged = RowRange.Cells(1, 1).Value

    For j = 2 To RowRange.Count
        merged = merged & delimiter & RowRange.Cells(1, j).Value
    Next j
    RowRange.Clear
    RowRange.Cells(1, 1).Value = merged
Next i
End Sub

Open in new window

0
 
LVL 5

Expert Comment

by:Kannan K
ID: 35196483
Hi,

Please ignore the previouse code,

KK,
0
 
LVL 5

Assisted Solution

by:Kannan K
Kannan K earned 500 total points
ID: 35196492
Hi,

Sorry, some problem in the attach code, please take from here,

Sub MergeColumn()

Dim merged As String
Dim delimiter As String
Dim NumRows As Integer
Dim NumCols As Integer

delimiter = InputBox("Enter delimeter Symbol", "Merge Columns")
NumRows = InputBox("Enter Number of Rows:", "Merge Columns")
NumCols = InputBox("Enter Number of Columns:", "Merge Columns")

For Each i In Range(Selection.Cells(1, 1), Selection.Cells(NumRows, NumCols))
    Set RowRange = Range(i.Cells(1, 1), i.EntireRow.End(xlToRight))
    merged = RowRange.Cells(1, 1).Value
    ' append remaining columns
    For j = 2 To RowRange.Count
        merged = merged & delimiter & RowRange.Cells(1, j).Value
    Next j
    RowRange.Clear
    RowRange.Cells(1, 1).Value = merged
Next i
End Sub

KK,
0
 
LVL 1

Expert Comment

by:martyk
ID: 35199853
Hey KK

Looks like you copied and pasted most of my code but didn't understand it.

It was written specifically so that the user would not need to know or input the number of rows and columns. The code figures it out directly from the data. Importantly, it allows for each row to have a different number of columns as may be the case in the real world.

Marty
0
 

Author Comment

by:tahirih
ID: 35200302
Thank you everyone - your patience is appreciated while I review.
0
 

Author Closing Comment

by:tahirih
ID: 35202208
Thank you - everyone had information that was essential.
0
 
LVL 5

Expert Comment

by:Kannan K
ID: 35204708
martyk,

Yes, most of the places I have followed your method only, Thanks for that. But as per your code, the cursor has gone to search the end of Line (Last cell of row) and End of Column (Last cell of each column). so it is travelling to the entire sheet. because of that I have modified your approach.

KK,
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question