Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about parsing a column of cells with a comma in text
Hello,
I have a column of cells that some of the cells have data like the following 124 Main Street, Suite 250 In a perfect world I would like to take the information after the comma and move it to a cell next to it. Then delete the comma. Just posting on a remote chance that someone has done this before. Thanks Larry C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about parsing a column of cells with a comma in text
Try some code like the following. Select the cells you want to change and
then run the code. Sub SplitAddress() Dim Rng As Range Dim Arr As Variant For Each Rng In Selection.Cells If Rng.Text < vbNullString Then Arr = Split(Rng.Text, ",") Rng.Value = Arr(LBound(Arr)) If UBound(Arr) LBound(Arr) Then Rng(1, 2).Value = Arr(LBound(Arr) + 1) End If End If Next Rng End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Larry C" wrote in message ... Hello, I have a column of cells that some of the cells have data like the following 124 Main Street, Suite 250 In a perfect world I would like to take the information after the comma and move it to a cell next to it. Then delete the comma. Just posting on a remote chance that someone has done this before. Thanks Larry C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about parsing a column of cells with a comma in text
Try selecting the column of addresses.
Then Data|Text to columns (in the xl2003 menu system) Delimited (by commas) and finish up the wizard. If you need a macro, record one when you do it manually. Larry C wrote: Hello, I have a column of cells that some of the cells have data like the following 124 Main Street, Suite 250 In a perfect world I would like to take the information after the comma and move it to a cell next to it. Then delete the comma. Just posting on a remote chance that someone has done this before. Thanks Larry C -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about parsing a column of cells with a comma in text
Thanks Guys
Both ideas are huge help Larry C "Dave Peterson" wrote in message ... Try selecting the column of addresses. Then Data|Text to columns (in the xl2003 menu system) Delimited (by commas) and finish up the wizard. If you need a macro, record one when you do it manually. Larry C wrote: Hello, I have a column of cells that some of the cells have data like the following 124 Main Street, Suite 250 In a perfect world I would like to take the information after the comma and move it to a cell next to it. Then delete the comma. Just posting on a remote chance that someone has done this before. Thanks Larry C -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parsing name in cell by comma? | Excel Worksheet Functions | |||
text parsing/table creation from single column data | Excel Discussion (Misc queries) | |||
Parsing a text string into separate cells | Excel Worksheet Functions | |||
Turning column contents into text list separated by a comma | Excel Discussion (Misc queries) | |||
parsing text in a multiple column count | Excel Worksheet Functions |