Split Cell values

[mijaz]

I have following data in each cell of Column D; for example cell D1 contains:

1
2
3
4
5
Above are the values in D1, similar data exists from D1 to D10000. I need to split these values into adjacent empty columns(cells). Text to columns does not work here because separating value is Alt+Enter which Text to Columns does not recognize. I probably need some VBA code for this. And need your help in this regard.

 

[Peter_SSs]

Welcome to the MrExcel board!

I have moved your thread to the Excel Questions forum from the Test Here Forum since it seemed like a legitimate question rather than just a test.

Text to Columns should work for with Alt+Enter as the delimiter. Click in the "Other:" box in the Text to Columns wizard, hold the ALT key down and on the number keypad type 0010 and continue with the wizard.

 

[mijaz]

Thanks for your guidance. But unfortunately, both of the below don't work for me i.e. Alt+Enter does nothing in Text to columns nor do typing 0010 helps while holding Alt Key. I hope I am not messing it up.

Thanking you again for your cooperation and support.

 

[hiker95]

mijaz,


Sample raw data:

Excel Workbook
	D	E	F	G	H	I
1	12345
2	123
3	1011121314
4
Sheet1

After the first version of the macro:

Excel Workbook
	D	E	F	G	H	I
1	12345	1	2	3	4	5
2	123	1	2	3
3	1011121314	10	11	12	13	14
4
Sheet1

After the second version of the macro:

Excel Workbook
	D	E	F	G	H	I
1	1	2	3	4	5
2	1	2	3
3	10	11	12	13	14
4
Sheet1

Which version do you prefer?

 

[Peter_SSs]

Thanks for your guidance. But unfortunately, both of the below don't work for me i.e. Alt+Enter does nothing in Text to columns nor do typing 0010 helps while holding Alt Key. I hope I am not messing it up.

Thanking you again for your cooperation and support.

Text to Columns does work with Alt+Enter.

1. Are you certain the characters separating your numbers are Alt+Enter? If your original sample data shown in your first post was in A1, what does this formula, placed in a vacant cell, return?
=CODE(MID(A1,2,1))

2. Can you confirm that with the method I suggested above that you typed the 0010 on the numeric keypad on the right of the keyboard, not the numbers above the letters?

 

[mijaz]

Meant for Mr. Peter_SSs: I have checked it again; I seldom use text to columns but the feature of Alt+Enter is not being accepted at least in my case.

Yes I am sure, the separation is being made via Alt+Enter. By applying =CODE(MID(A1,2,1)) system returns value= 97.

I am using a lap top which has no side key pad. So I use numeric keys above alphabets. But I will try this code on some system as well.


Meant for hiker95: Well thanks for your support, could you please provide both the codes? Please do mention the best way of learning it because I have some basic know how regarding how to record and use a macro but writing it is really a tough deal.



Thanks to both of the gentlemen.

Muhammad Ijaz Ch.

 

[hiker95]

mijaz,

Meant for hiker95: Well thanks for your support, could you please provide both the codes?

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitDVersion1()
' hiker95, 08/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=571933
Dim c As Range, Sp
Application.ScreenUpdating = False
For Each c In Range("D1", Range("D" & Rows.Count).End(xlUp))
  Sp = Split(c, Chr(10))
  c.Offset(, 1).Resize(, UBound(Sp) + 1) = Sp
Next c
Application.ScreenUpdating = True
End Sub


Sub SplitDVersion2()
' hiker95, 08/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=571933
Dim c As Range, Sp
Application.ScreenUpdating = False
For Each c In Range("D1", Range("D" & Rows.Count).End(xlUp))
  Sp = Split(c, Chr(10))
  c.Resize(, UBound(Sp) + 1) = Sp
Next c
Application.ScreenUpdating = True
End Sub

Please run each version on a separate active worksheet containing the same raw data.


Then run the SplitDVersion1 on a separate active worksheet containing the same raw data.


Then run the SplitDVersion2 on a separate active worksheet containing the same raw data.

 

[hiker95]

mijaz,

Please do mention the best way of learning it because I have some basic know how regarding how to record and use a macro but writing it is really a tough deal.

Training / Books / Sites

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a Visual Basic Module?
http://www.emagenit.com/VBA Folder/what_is_a_vba_module.htm

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

Excel Tutorials / Video Tutorials - Functions
http://www.contextures.com/xlFunctions02.html

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Cascading queries

http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

Excel VLOOKUP Function and VLOOKUP Example
http://www.contextures.com/xlFunctions02.html

INDEX MATCH - Excel Index Function and Excel Match Function
http://www.contextures.com/xlFunctions03.html

http://www.contextures.com/xlDataVal02.html
http://www.contextures.com/xlDataVal05.html
http://www.contextures.com/xlDataVal08.html#Larger

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)
by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

Professional Excel Development
by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)

Excel 2002 VBA: Programmers Reference
by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)

VB & VBA in a Nutshell: The Language
(http://www.amazon.co.uk/VB-VBA-Nutsh...4671189&sr=1-2)

Writing Excel Macros with VBA
(http://www.amazon.co.uk/Writing-Exce...4671189&sr=1-3)

User Form Creation
http://www.contextures.com/xlUserForm01.html

DonkeyOte: My Recommended Reading
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.xtremevbtalk.com/showthread.php?t=296012

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Sync Pivot Tables
http://www.mrexcel.com/forum/showthr...g+pivot+tables
Check out rorya's post at the very bottom of this link. It should do the trick:
http://www.experts-exchange.com/Soft..._22844558.html
Multiple pivot tables 1 filter to control all
http://www.excelforum.com/excel-prog...ntrol-all.html

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Function Dictionary
http://www.xlfdic.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/
http://www.exceldashboardwidgets.com/

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

 

[Peter_SSs]

Yes I am sure, the separation is being made via Alt+Enter. By applying =CODE(MID(A1,2,1)) system returns value= 97

A result of 97 indicates that the second character in cell A1 is an 'a' not Alt+Enter. However, that was probably my mistake for not getting you to point the formula at D1 instead of A1.

I am using a lap top which has no side key pad. So I use numeric keys above alphabets.

The manual method I suggested will not work with the numbers above the letters, which is why I kept emphasising using the number keypad.

However, Text to Columns will still work with Alt+Enter by using code. There should be no need to cycle through and split each worksheet row individually.

Sub TTC()
    With Range("D1", Range("D" & Rows.Count).End(xlUp))
        .TextToColumns Destination:=.Offset(, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
            Other:=True, OtherChar:="" & Chr(10) & ""
    End With
End Sub

Apart from doing the whole column of values at once, this code has the added advantage that it will not error if there happens to be any blank cells among the data in column D.

If you want to overwrite the original data (like hiker95's last example in post #4), just remove the red part of the code.

 

[Rick Rothstein]

I am using a lap top which has no side key pad. So I use numeric keys above alphabets. But I will try this code on some system as well.

You do have a number pad... it is embedded in your letter keys. I don't have a laptop handy now, but if you look, you can see the numbers on some of the keys on the right side of your keyboard in the upper left corner of the keys... those are your number pad. Somewhere up with your function keys is a function key toggle to make those keys into a number pad. However, you don't need to use the number pad to enter the Alt+Enter keystroke of ALT+0010 (which is a Line Feed by the way)... there is a direct keyboard keystroke for it. Follow Peter's instructions completely, but when you get to the part about keying in ALT+0010 from the number pad, press CTRL+J instead... that will produce the same result.