Solved

Excel - Undo delimeter text to columns

Posted on 2011-03-18
16
616 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

751 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