Solved

# Split a cell by clicking on the cell

Posted on 2011-04-27
216 Views
Last Modified: 2012-05-11
Split a cell by clicking on the cell
0
Question by:cd_morris
4 Comments

LVL 81

Expert Comment

Can you provide a few more details? What is in the cell? How do you want it split? A split implies splitting a cell into multiple cells - which cells do you want to get the results of the split?

Kevin
0

LVL 4

Author Comment

Kevin, this is a follow up question to Q_26976800.html

I have data in cell example D1 (but could change) that looks like the following:

1. This is a test.
2. This is another test.

I want to be able to double click on the cell and have the data split to have :
"1. This is a test." in one cell and "2. This is another test." in the cell under it.
0

LVL 81

Accepted Solution

Add this code to the worksheet code module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Lines As Variant
Lines = Split(Target.Value, vbLf)
Target.Resize(UBound(Lines) + 1).Value = Application.Transpose(Lines)

End Sub

You don't need any of the code from the other question to do this.

Kevin
0

LVL 81

Expert Comment

This one is a little more robust and will handle a cell with multiple sentences without line feeds:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Lines As Variant
If Len(Target) > 0 Then
Lines = Split(Replace(Target.Value, vbLf, " "), ". ")
Target.Resize(UBound(Lines) + 1).Value = Application.Transpose(Lines)
End If

End Sub

Kevin
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!