Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns doesn't work for this problem.
Im not a programmer so can someone help me with code for the problem listed below. Text to Columns doesnt work because some cells have data separated by three commas and others only two commas.
Example of original column is: Column A Street address, suite no., city, state zip Street address, city, state zip I need to start at the right and extract the data up to the first comma and second comma (but not the third comma), individually into their own columns. I start with one column and end up with three columns. The problem I have is that there is sometimes an extra comma because some have addresses and suite no. and others just have address. Examples of new columns a Column A Column B Column C Street address, suite no. city state zip Street address, city state zip If I use the text to columns some records have four columns and some three. So they don't fall in the correct column and have to be moved around. Also, if there is an address and suite no. they have to be put back together. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns doesn't work for this problem.
Select your data in column A and run the macro
Sub AAAA() Dim sStr As String Dim cell As Range For Each cell In Selection sStr = cell.Value varr = Split(sStr, ",") cell.Offset(0, 2) = Trim(varr(UBound(varr))) cell.Offset(0, 1) = Trim(varr(UBound(varr) - 1)) If UBound(varr) = 3 Then cell.Value = Trim(varr(0) & ", " & varr(1)) Else cell.Value = Trim(varr(0)) End If Next End Sub -- Regards, Tom Ogilvy "Ann Vlna" wrote in message ... I'm not a programmer so can someone help me with code for the problem listed below. Text to Columns doesn't work because some cells have data separated by three commas and others only two commas. Example of original column is: Column A Street address, suite no., city, state zip Street address, city, state zip I need to start at the right and extract the data up to the first comma and second comma (but not the third comma), individually into their own columns. I start with one column and end up with three columns. The problem I have is that there is sometimes an extra comma because some have addresses and suite no. and others just have address. Examples of new columns a Column A Column B Column C Street address, suite no. city state zip Street address, city state zip If I use the text to columns some records have four columns and some three. So they don't fall in the correct column and have to be moved around. Also, if there is an address and suite no. they have to be put back together. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns doesn't work for this problem.
Tom, thank you so much for the code. It did exactly what I wanted it to. You have saved me many hours of work and I appreciate it.
"Tom Ogilvy" wrote: Select your data in column A and run the macro Sub AAAA() Dim sStr As String Dim cell As Range For Each cell In Selection sStr = cell.Value varr = Split(sStr, ",") cell.Offset(0, 2) = Trim(varr(UBound(varr))) cell.Offset(0, 1) = Trim(varr(UBound(varr) - 1)) If UBound(varr) = 3 Then cell.Value = Trim(varr(0) & ", " & varr(1)) Else cell.Value = Trim(varr(0)) End If Next End Sub -- Regards, Tom Ogilvy "Ann Vlna" wrote in message ... I'm not a programmer so can someone help me with code for the problem listed below. Text to Columns doesn't work because some cells have data separated by three commas and others only two commas. Example of original column is: Column A Street address, suite no., city, state zip Street address, city, state zip I need to start at the right and extract the data up to the first comma and second comma (but not the third comma), individually into their own columns. I start with one column and end up with three columns. The problem I have is that there is sometimes an extra comma because some have addresses and suite no. and others just have address. Examples of new columns a Column A Column B Column C Street address, suite no. city state zip Street address, city state zip If I use the text to columns some records have four columns and some three. So they don't fall in the correct column and have to be moved around. Also, if there is an address and suite no. they have to be put back together. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to Columns Problem | Excel Discussion (Misc queries) | |||
Text to columns with date format does not work :( | Excel Worksheet Functions | |||
Text to Columns doesn't work | Excel Discussion (Misc queries) | |||
Copying Columns of Text - WS Functions do not work correctly | Excel Worksheet Functions | |||
Convert Text to Columns with Excel 2007 beta does not work well | Excel Worksheet Functions |