Macro to split the contents in a single cell separated by "," into next cell in that column

Hi,

I have sheet 1:

        column A    column B  columnC
        WR#          Phase       SP#
Row2 60625       1-0110       60625RB1,60625NS1,60625GW1,60625BB1

Expected Output on clicking a button:

         column A    column B  columnC
         WR#          Phase       SP#
Row2 60625       1-0110       60625RB1
Row3                                  60625NS1
Row4                                 60625GW1
Row5                                 60625BB1


Is there a way to split the contents in cell C2 separated by comma (,)
and place it on next cell in column C itself?
Please suggest.Thankyou.



0
anshu
1/21/2010 4:29:05 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
2493 Views

Similar Articles

[PageSpeed] 19

On Jan 21, 11:29=A0am, anshu minocha <anshu.ki...@gmail.com> wrote:
> Hi,
>
> I have sheet 1:
>
> =A0 =A0 =A0 =A0 column A =A0 =A0column B =A0columnC
> =A0 =A0 =A0 =A0 WR# =A0 =A0 =A0 =A0 =A0Phase =A0 =A0 =A0 SP#
> Row2 60625 =A0 =A0 =A0 1-0110 =A0 =A0 =A0 60625RB1,60625NS1,60625GW1,6062=
5BB1
>
> Expected Output on clicking a button:
>
> =A0 =A0 =A0 =A0 =A0column A =A0 =A0column B =A0columnC
> =A0 =A0 =A0 =A0 =A0WR# =A0 =A0 =A0 =A0 =A0Phase =A0 =A0 =A0 SP#
> Row2 60625 =A0 =A0 =A0 1-0110 =A0 =A0 =A0 60625RB1
> Row3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A06=
0625NS1
> Row4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 6062=
5GW1
> Row5 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 6062=
5BB1
>
> Is there a way to split the contents in cell C2 separated by comma (,)
> and place it on next cell in column C itself?
> Please suggest.Thankyou.

0
anshu
1/21/2010 4:59:10 PM
Found the code:
http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/3d65155e864728a5/a15587d778c2c275?hl=en&lnk=gst&q=Split+contents+of+a+cell#a15587d778c2c275
thanks
0
anshu
1/21/2010 4:59:36 PM
Here is another way to do it...

Sub SplitDownwardColumnC()
  Dim X As Long, LastRow As Long, StartRow As Long
  Dim Cell As Range, Parts As Variant
  StartRow = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = LastRow To StartRow Step -1
    Parts = Split(Cells(X, "C").Value, ",")
    Rows(X).Offset(1).Resize(UBound(Parts)).Insert
    Cells(X, "C").Resize(UBound(Parts) + 1).Value = _
                WorksheetFunction.Transpose(Parts)
  Next
End Sub

-- 
Rick (MVP - Excel)


"anshu minocha" <anshu.kikli@gmail.com> wrote in message 
news:c7b863c4-b3e3-4697-b494-c1cc7c985c7a@s3g2000vbp.googlegroups.com...
> Found the code:
> http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/3d65155e864728a5/a15587d778c2c275?hl=en&lnk=gst&q=Split+contents+of+a+cell#a15587d778c2c275
> thanks 

0
Rick
1/21/2010 8:01:54 PM
Reply:

Similar Artilces:

splitting text to array,is it possible?
the text is seperated by "_", like("abc_xyz_spq") and is not set how many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc). I want to do something to each text(abc,xyz,spq, etc). How can I split them without VBA. StringTokenizer-like function. Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore -- Kind regards, Niek Otten Microsoft MVP - Excel "kang" <kang@hao.com> wrote in message news:eM9BE0W6HHA.4880@TK2MSFTNGP03.phx.gbl... | the text is seperated by "_", l...

Where does Word 2007 keep my macros, toolbar customizations etc?
I have a new computer running Win 7 and it has Microsoft Office 2007. the computer had a RAID 1 configuration, which means a second disk was mirroring everything the main disk was doing. The main disk failed, and I for a number of reason i was force to do a fresh reinstall of Win7 and Office 2007 on a new main drive. I am now trying to migrate my data from disk 2 to disk 1. I have managed to copy over all my documents, which was straightforward. But i also to copy the macros, toolbar settings etc from 2 to drive 1, but i don't know what files i should be transferring. A...

Macro Warning, But No Macros!
Running Excel 2003 SP1 on a Windows 2000 Server machine. I have an old spreadsheet that when I open it, I get the macro warning: "Macros are disabled because the security level is set to High and a digitally signed Trusted Certificate is not attached to the macros. To run the macros, change the security level to a lower setting (not recommended), or request the macros be signed by the author using a certificate issued by a Certificate Authority." When I go to look for any macros in the workbook (ALT-F8), the list is blank. Where else could there be a macro that would trigger this...

Name Cell, Copy Named Cell, And Add All Named Cells
I'd like to be able to have a cell that is able to be copied and then have another location that adds the original cell and any copies of that cell. In my situation, I have a group of cells that get copied different numbers of times each time I use the document. There is one cell in that group and all the copies that need to be added, but I never know how many times I'll be copy that group. Is there a trick for doing this? Thanks. There may be a trick but I don't understand your request. Can you use some cell references for examples. Gord Dibben MS Excel MV...

Copying exact formula instead, rather than with usu. Excel's helpful cell adjustments/increments?
Excel's mthod of copying formulas while taking into account new cells is usu. really vital. i.e., a formula in A1 when copy/pasted into B1 automatically copies adjusted for B1, C1 will show C1, etc. Every once in a blue moon, though, we need to copy the exact formula when the information just needs to be replicated exactly in other cells. Is there a special keystroke to do that? Tx. Set the cell references as absolute, e.g. $A$2 instead of A2 -- HTH RP (remove nothere from the email address if mailing direct) "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam...

create a cell that accumulates, numbers from another cell...
I need to know how to have a entery cell that can send number values to a cell that accumilates there sum .the entries in the entery cell can be changed and the recieving cell just keeps adding up. The entry cell can be left blank and the sum remains in recieving cell. -- Thanks sombull http://www.mcgimpsey.com/excel/accumulator.html -- Regards, Peo Sjoblom "sombull" <sombull@discussions.microsoft.com> wrote in message news:41021B44-363C-418C-9A4F-6CD126431698@microsoft.com... > I need to know how to have a entery cell that can send number values to a > cell ...

Browsers, file associations, content-types: HELP!!
I'm trying to understand how a regular windows application can be started when the user clicks on a link to a particular filetype in a browser. Background (in case its important) I have a normal windows app which has registered its filetype (say ".xyz") so that double-clicking on a ".xyz" file in Windows Explorer opens the ".xyz" document in my application. This is done by a few registry keys: HKEY_CLASSES_ROOT/.xyz "MyPrivate.Doc" HKEY_CLASSES_ROOT/MyPrivate.Doc/shell/open/command "<path to app>" (I've omit...

Cells won't convert to number format, even after format/cells/num.
In excel, I tried to convert numbers to number format, using format/cells/number from the category list. But it won't take, and stays text-like. Any ideas? If it turns out that you have spaces (or those non-breaking spaces), David McRitchie has some code that will help clean this junk up: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") But after you format the cell as general, try selecting the cell, then hit F2, then enter. You have to make a change to the cell for the value to change from text to number. scottr wrote: > > In excel,...

Copied web pg to Excel, why can't I find cell of a pic I can see?
I copied a web page to Excel to manipulate the information on it. I a trying to put that info onto another worksheet. On the firs worksheet, where I copied and pasted the web page, I can see a picture When I try to find what cell that picture is in, I cannot find it. Is it not possible to copy pictures? I see something in Excel hel that says .gif images are not copied. I have one of those also that want to use, and I can see it on the first worksheet, just cannot fin a cell for it. Thanks. dmv -- Message posted from http://www.ExcelForum.com ...

hi All i'm a fresher in VB macro, need a script for generating Purchase order
i'm a fresher having not much knowledge on VB macros, i'm looking for macro script to generate Purchase order from one sheetto another shee in the same work book but with excel Purchase order template i' sending the attahcment with this if anyone could do the ned fu -- sanj ----------------------------------------------------------------------- sanju's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1650 View this thread: http://www.excelforum.com/showthread.php?threadid=31367 ...

How can I spread a column with 5,000 numbers into multiple column.
I have imported data in one column that is 5,000 cells long. How can I make this into mulitple columns so it is easier to view/print? Hi, See: http://www.mvps.org/dmcritchie/excel/snakecol.htm >-----Original Message----- >I have imported data in one column that is 5,000 cells long. How can I make >this into mulitple columns so it is easier to view/print? >. > Manually............ If your data is an column A starting at Cell A1, then the following formula, entered in Cell B1 and filled across 10 columns and down 500 rows will produce your 10 columns of 500 rows. Any more t...

highlight cells equals sum, not count
When I highlight a block of cells the total would show at the bottom right of the screen. Now, when I highlight the cells, I get 'Count = x" instead of $sum. Why and how to fix. Thanks! Hi PTFisher Right click on "'Count = x" and change it -- Regards Ron de Bruin http://www.rondebruin.nl "PTFisher" <PTFisher@discussions.microsoft.com> wrote in message news:0EC51005-6359-4F6D-96E2-0AAEEF469551@microsoft.com... > When I highlight a block of cells the total would show at the bottom right of > the screen. Now, when I highlight the cells, I get ...

formula or macro? whichever, I need help
I have a cell/column with 9-digit social security numbers, no spaces, no dashes. I need the last 4 numbers of each cell copied into a seperate cell/column. I can't find anything about copying partial cell value. Can anyone please help? Thank you, Dagmar Livingston, Columbus, GA =RIGHT(A1,4) -- Kind regards, Niek Otten "daggyfresh" <daggyfresh@discussions.microsoft.com> wrote in message news:AD76AFE1-51CF-426F-A09B-DD8A76696122@microsoft.com... >I have a cell/column with 9-digit social security numbers, no spaces, no > dashes. I need the last 4 numbers of...

255 Character Truncation, Vlookup Macro vs. Opening Workbook Macro
Hello, I just posted an entry on the microsoft.public.excel.programming forum, but thought I'd post it here as well. http://groups-beta.google.com/group/microsoft.public.excel.programming/browse_thread/thread/40fae474a64d6900/ce8e97e11e429402#ce8e97e11e429402 I forgot to complete the subject line on that post. Any ideas? But you already have a response in .programming. It's pretty unusual that you'd have to post to multiple forums. But if you think you must, try to post to both at one time. jrew23@yahoo.com wrote: > > Hello, > > I just posted an entry on the mi...

Moving within a range, using a macro!!
I have a macro that says, Range("B1").Select, now I want to move within a range automatically,and I want to be able to exit by hitting the ESC button. The spreadsheet looks like this: A B C D 1 Beginning 10000 10000 10000 2 Today's 3 Total 10000 10000 10000 4 Sales There are more lines to this, but this is the general idea, I would like to move to the right and skip a line and move down to the next empty cell, or exit. Why do you need to move to the cell? Selections are usually NOT ...

Extract city, state and zip code from a single cell
is there a way to Extract city, state and zip code from a single cell sample data: 4076 St. Andrews Ct. Canfield, OH 44406 Hi Try to use Data> Text to Columns in the menu bar -- Regards Ron de Bruin http://www.rondebruin.nl <jajoseph@zoominternet.net> wrote in message news:1107674941.810942.162640@c13g2000cwb.googlegroups.com... > is there a way to Extract city, state and zip code from a single cell > > sample data: > > 4076 St. Andrews Ct. Canfield, OH 44406 > Is there a way to do it without using Text to Columns in the menu ba hi any reason why you...

words splitting up in tables, word wrap
Hi, I know it must be a setting, but when I am doing text in a table how do I get the words to stay complete instead of breaking up at the end of the lines? thanks Bruce Take a look at Tools, Options Edit tab Uncheck auto Hyphenation. don vancouver, usa "Bruce" <notreally@idontwantoo.com> wrote in message news:eOaP8HyGHHA.420@TK2MSFTNGP06.phx.gbl... > Hi, > > I know it must be a setting, but when I am doing text in a table how do I > get the words to stay complete instead of breaking up at the end of the > lines? > thanks Bruce > ...

Cond. Formatting, Mod(Row) and cell conditions
Is there any way to use the mod row function to shade alternative rows, based on whether cells have any value in them. I want to select a big range (eg A:G) and only shade alternative rows (A:G) once data is place in cells. the mod row function shades regardless if there is data in or not. Select ColA:G and try the below CF formula..Make sure the active cell of your selection is in Row1 =AND(MOD(ROW(),2),COUNTA($A1:$G1)) -- Jacob (MVP - Excel) "wynand" wrote: > Is there any way to use the mod row function to shade alternative rows, based > on wheth...

match and index function
I am using the match and index function to pull in data from a separate worksheet. I am using Column A as the reference cell, whereas my formula lies in Column F. The formula pulls in the data fine. The problem is, when I sort the data set, the match index function maintains the original reference cell and does NOT refer to the same row. So, for example, if my formula references A3, the formula continues to use A3 after I sort the cell (as if I had used an absolute reference). Does anyone know how to fix this? How can I pull in data using matchindex (or vlookup), but also be able to sort ...

Excel 2003, when I click on a particular cell it gets deselected
While using Excel 2003, when I click on a particular cell, within 30 seconds, that particular cell gets deselected followed by the workbook. So each time I need to click either the cell or workbook to enter data in that particular sheet. There could be event code that is being fired. Does this happen if you open the workbook with macros disabled? HTH, Bernie MS Excel MVP "towinwin" <towinwin@discussions.microsoft.com> wrote in message news:B7B87AFB-27AC-4235-9AE3-5E7A46310AF8@microsoft.com... > While using Excel 2003, when I click on a particular cell, within 30 se...

Macro, being taken to Macro text when execution
I am using the below code to color code the names if they appear in th cells below the heaing Pilot, when I execute the macro I am taken t the macro with the End Sub highlighted. Not sure what I am doing wron here, any help is appericated. Sub Colorcells() For Line = 150 To 1 Step -1 If Pilot = "Lyon" Or Pilot = "Post" Or Pilot = "Hall" Or Pilot "Cabot" Or Pilot = "Baganai" Or Pilot = "Cline" Or Pilot = "Goldfein Or Pilot = "Fink" Then Cells(Line, 7).Select Selection.Font.ColorIndex = 5 End If End Su -- Message pos...

HELP: Running two Macros, one before the other
I have this Excel spreadsheet in which I want to automate, I have two macros once called Macro_GetData, and Macro_FormatData. Both these Macros work fine, but when I try to automate them it runs the 2nd Macro before the 1st Macro is even finish. I tried to use the "Application.Wait" command but that just stopped the macros entirely. What I want the macro to do is to run the first Macro (Macro_GetData) and when that's finished then run the second Macro (Macro_FormatData). The second Macro cannot work unless the first macro is completed. How can I go about doing this? Bobbak, It s...

Excel says macro, but no macros present?
I have a certain excel file that I use daily, and everytime I open thi file it gives the old "This file contains macros - do you want t enable them or not?" warning. However, as far as I know, the fil doesn't contain any macros. There's nothing listed in Tools -> Macros There are no modules. No unusual add-ins. And no unusual references. I only have this problem with this single file, and no others. Viru scan turns up nothing. Any ideas -- Ph ----------------------------------------------------------------------- Pho's Profile: http://www.excelforum.com/member....

Selecting a named range, the name of the named range is in a cell
What i want to do is the following : I have a cell which i have named "ExtraRisk". This cell contains text : example "Electricity", which is also the name of a range. Now i want to select this named range "Electricity" & copy the range How do i do this. What i have so far is : Sub Risicos_toevoegen() ..... Sheets("MASTER").Select 'I'm stuck here.... Application.Goto Reference:="ExtraRisk" 'I do not want to copy the named range "ExtraRisk", but i want to copy the named range where the va...

Macro Help, should be an easy solution
Hi to All, My question is this: I am trying to create a Macro that will change a Fixed cell: SAY D2: In D2, I have a formula that says "=C9". What I am trying to accomplish is to have a Macro button that I click on and it automatically changing Cell D2, which has "=C9" to "=D9". The trick is that I want to be able to click that button again and it to change the formula to "=E9" and so on "=F9" etc... I can't seem to figure this out and would appreciate some guidance. Let me know if this is not enough info to complete this task. I kn...