Solved

Excel - Undo delimeter text to columns

Posted on 2011-03-18
16
607 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 125 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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 125 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 125 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 125 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

830 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