How to delete commas and spaces when not needed ... S&R way, or macro way?
I was wondering how to clean up a couple of columns. I have
concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
With the list starting in B5... Fill column C with trimmed data (remove leading/trailing spaces) Use this formula in D5 _ and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5) Use this formula in E5 _ and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5) Copy and paste values. -- Jim Cone Portland, Oregon USA (You don't accept Spam, I hope you don't send any) "StargateFan" wrote in message I was wondering how to clean up a couple of columns. I have concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
wrote: With the list starting in B5... Fill column C with trimmed data (remove leading/trailing spaces) Is there a way to automate removing leading/trailing spaces? That would be so cool. The fields have more than one word in them so that I can't just do a S&R for spaces. I'm guessing that the leading/trailing spaces are why this doesn't work 100% ... (?) Use this formula in D5 _ and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5) Use this formula in E5 _ and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5) Copy and paste values. -- Jim Cone Portland, Oregon USA (You don't accept Spam, I hope you don't send any) <lol I sure as heck don't! Never have, in all my years, though it's just an easy way to put an email address vs. a munged one. <g "StargateFan" wrote in message I was wondering how to clean up a couple of columns. I have concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
How about a macro? Just change the two Const (constant) assignments to the
column letter and sheet name where your data is located at... Sub CleanUpCommaSpaces() Dim X As Long Dim R As Range Dim LastRow As Long Const ColumnLetter As String = "A" Const SheetName As String = "Sheet6" With Worksheets(SheetName) LastRow = .Columns(ColumnLetter).SpecialCells(xlCellTypeLast Cell).Row For X = 1 To LastRow With .Cells(X, ColumnLetter) If .Value Like ", *" Then .Value = Mid(.Value, 3) ElseIf .Value Like "*, " Then .Value = Left(.Value, Len(.Value) - 2) End If End With Next End With End Sub -- Rick (MVP - Excel) "StargateFan" wrote in message ... On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone" wrote: With the list starting in B5... Fill column C with trimmed data (remove leading/trailing spaces) Is there a way to automate removing leading/trailing spaces? That would be so cool. The fields have more than one word in them so that I can't just do a S&R for spaces. I'm guessing that the leading/trailing spaces are why this doesn't work 100% ... (?) Use this formula in D5 _ and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5) Use this formula in E5 _ and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5) Copy and paste values. -- Jim Cone Portland, Oregon USA (You don't accept Spam, I hope you don't send any) <lol I sure as heck don't! Never have, in all my years, though it's just an easy way to put an email address vs. a munged one. <g "StargateFan" wrote in message I was wondering how to clean up a couple of columns. I have concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
On Tue, 2 Jun 2009 03:54:57 -0400, "Rick Rothstein"
wrote: How about a macro? Just change the two Const (constant) assignments to the column letter and sheet name where your data is located at... Macro sounds great, as long as it knows only to delete the values where there is nothing before the ", " or after it, as sometimes there are 2 words that need separating. Thanks, will give this a try at work later this morning! :oD Sub CleanUpCommaSpaces() Dim X As Long Dim R As Range Dim LastRow As Long Const ColumnLetter As String = "A" Const SheetName As String = "Sheet6" With Worksheets(SheetName) LastRow = .Columns(ColumnLetter).SpecialCells(xlCellTypeLast Cell).Row For X = 1 To LastRow With .Cells(X, ColumnLetter) If .Value Like ", *" Then .Value = Mid(.Value, 3) ElseIf .Value Like "*, " Then .Value = Left(.Value, Len(.Value) - 2) End If End With Next End With End Sub -- Rick (MVP - Excel) "StargateFan" wrote in message .. . On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone" wrote: With the list starting in B5... Fill column C with trimmed data (remove leading/trailing spaces) Is there a way to automate removing leading/trailing spaces? That would be so cool. The fields have more than one word in them so that I can't just do a S&R for spaces. I'm guessing that the leading/trailing spaces are why this doesn't work 100% ... (?) Use this formula in D5 _ and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5) Use this formula in E5 _ and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5) Copy and paste values. -- Jim Cone Portland, Oregon USA (You don't accept Spam, I hope you don't send any) <lol I sure as heck don't! Never have, in all my years, though it's just an easy way to put an email address vs. a munged one. <g "StargateFan" wrote in message I was wondering how to clean up a couple of columns. I have concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
On Tue, 2 Jun 2009 03:54:57 -0400, "Rick Rothstein"
wrote: How about a macro? Just change the two Const (constant) assignments to the column letter and sheet name where your data is located at... Sub CleanUpCommaSpaces() Dim X As Long Dim R As Range Dim LastRow As Long Const ColumnLetter As String = "A" Const SheetName As String = "Sheet6" With Worksheets(SheetName) LastRow = .Columns(ColumnLetter).SpecialCells(xlCellTypeLast Cell).Row For X = 1 To LastRow With .Cells(X, ColumnLetter) If .Value Like ", *" Then .Value = Mid(.Value, 3) ElseIf .Value Like "*, " Then .Value = Left(.Value, Len(.Value) - 2) End If End With Next End With End Sub Oh, just noticed, will this do the whole workbook, or just sheetname "Sheet6", since all our sheets have specific labels. But what a good idea if the macro could handle the entire workbook at once! Also, does Const ColumnLetter As String = "A" mean that only column A will be handled? That would be great because I could then choose the actual real columns to fix each time, in this case, each worksheet has 2 columns. Thanks! :oD -- Rick (MVP - Excel) "StargateFan" wrote in message .. . On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone" wrote: With the list starting in B5... Fill column C with trimmed data (remove leading/trailing spaces) Is there a way to automate removing leading/trailing spaces? That would be so cool. The fields have more than one word in them so that I can't just do a S&R for spaces. I'm guessing that the leading/trailing spaces are why this doesn't work 100% ... (?) Use this formula in D5 _ and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5) Use this formula in E5 _ and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5) Copy and paste values. -- Jim Cone Portland, Oregon USA (You don't accept Spam, I hope you don't send any) <lol I sure as heck don't! Never have, in all my years, though it's just an easy way to put an email address vs. a munged one. <g "StargateFan" wrote in message I was wondering how to clean up a couple of columns. I have concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
How about a macro? Just change the two Const (constant) assignments to the
column letter and sheet name where your data is located at... Sub CleanUpCommaSpaces() Dim X As Long Dim R As Range Dim LastRow As Long Const ColumnLetter As String = "A" Const SheetName As String = "Sheet6" With Worksheets(SheetName) LastRow = .Columns(ColumnLetter).SpecialCells(xlCellTypeLast Cell).Row For X = 1 To LastRow With .Cells(X, ColumnLetter) If .Value Like ", *" Then .Value = Mid(.Value, 3) ElseIf .Value Like "*, " Then .Value = Left(.Value, Len(.Value) - 2) End If End With Next End With End Sub Oh, just noticed, will this do the whole workbook, or just sheetname "Sheet6", since all our sheets have specific labels. But what a good idea if the macro could handle the entire workbook at once! Also, does Const ColumnLetter As String = "A" mean that only column A will be handled? That would be great because I could then choose the actual real columns to fix each time, in this case, each worksheet has 2 columns. Okay, this macro will process each sheet in the workbook and, yes, it only does one column (the column letter that you assign to the ColumnLetter constant... it does this same column on each sheet). And to clarify, it only removes the comma-space (both characters must be present) from the beginning or the end of the text in a cell and no where else... and to further clarify, it only does this for cells contain fixed text (that is, cells whose value does NOT come from a formula)... Sub CleanUpCommaSpaces() Dim X As Long Dim R As Range Dim LastRow As Long Dim WS As Worksheet Const ColumnLetter As String = "A" On Error GoTo Whoops Application.ScreenUpdating = False For Each WS In Worksheets LastRow = WS.Columns(ColumnLetter).SpecialCells(xlCellTypeLa stCell).Row For X = 1 To LastRow With WS.Cells(X, ColumnLetter) If Not .HasFormula Then If .Value Like ", *" Then .Value = Mid(.Value, 3) ElseIf .Value Like "*, " Then .Value = Left(.Value, Len(.Value) - 2) End If End If End With Next Next Whoops: Application.ScreenUpdating = True End Sub -- Rick (MVP - Excel) |
How to delete commas and spaces when not needed ... S&R way, or macro way?
Concatenate the ranges using this UDF which ignores blank cells so you don't
get the extra spaces and commas when data is missing. Function ConCatRange(CellBlock As Range) As String 'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5)) Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & ", " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function Gord Dibben MS Excel MVP On Mon, 01 Jun 2009 20:08:35 -0500, StargateFan wrote: I was wondering how to clean up a couple of columns. I have concatenated two fields together that had blank cells in some places. The results I got after copy-pasting the concatenates formulas into values show up in these three ways, the first one being okay, the other two not: Address1, Address2 (okay) , Address2 (not okay, need to get rid of comma and space _before_ text) Address1, (also not okay, need to get rid of comma and space _after_ the text). Is there an easier, quicker way to clean up the entries of these commas and spaces where they're not needed vs. doing the clean up manually? Thanks! :oD |
How to delete commas and spaces when not needed ... S&R way, or macro way?
I thought you would know how to do this...
In C5 enter the formula =TRIM(B5) and fill down -- Jim Cone Portland, Oregon USA "StargateFan" wrote in message Is there a way to automate removing leading/trailing spaces? That would be so cool. The fields have more than one word in them so that I can't just do a S&R for spaces. I'm guessing that the leading/trailing spaces are why this doesn't work 100% ... (?) |
How to delete commas and spaces when not needed ... S&R way, or macro way?
I know he asked about leading/trailing spaces, but his original question
showed there were commas in front of the spaces that also had to be removed. In addition, your suggestion would change the text he wants to preserve if it happens to have multiple internal spaces (for formatting or column alignment as an example) since TRIM collapses multiple spaces down to single spaces. -- Rick (MVP - Excel) "Jim Cone" wrote in message ... I thought you would know how to do this... In C5 enter the formula =TRIM(B5) and fill down -- Jim Cone Portland, Oregon USA "StargateFan" wrote in message Is there a way to automate removing leading/trailing spaces? That would be so cool. The fields have more than one word in them so that I can't just do a S&R for spaces. I'm guessing that the leading/trailing spaces are why this doesn't work 100% ... (?) |
How to delete commas and spaces when not needed ... S&R way, or macro way?
Rick,
I think you missed my original post Jim Cone "Rick Rothstein" wrote in message I know he asked about leading/trailing spaces, but his original question showed there were commas in front of the spaces that also had to be removed. In addition, your suggestion would change the text he wants to preserve if it happens to have multiple internal spaces (for formatting or column alignment as an example) since TRIM collapses multiple spaces down to single spaces. -- Rick (MVP - Excel) |
How to delete commas and spaces when not needed ... S&R way, or macro way?
Yes, I did... sorry.
-- Rick (MVP - Excel) "Jim Cone" wrote in message ... Rick, I think you missed my original post Jim Cone "Rick Rothstein" wrote in message I know he asked about leading/trailing spaces, but his original question showed there were commas in front of the spaces that also had to be removed. In addition, your suggestion would change the text he wants to preserve if it happens to have multiple internal spaces (for formatting or column alignment as an example) since TRIM collapses multiple spaces down to single spaces. -- Rick (MVP - Excel) |
All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com