Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel - Undo delimeter text to columns

Posted on 2011-03-18
16
Medium Priority
?
634 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

972 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