Solved

Excel - Undo delimeter text to columns

Posted on 2011-03-18
16
601 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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