Conditional format for removing trailing and leading spaces.

[Eamonn100]

Hi,

I'm using this formula to remove any trailing or leading spaces. =TRIM(CLEAN(A1)) It works fine but is there anyway that I can use it as a conditional format so that when I paste the information onto the page it removes any trailing or leading spaces.

At the moment I have to use this formula on a second page to get the spaces removed. I'm trying to get this on to my original page that I paste onto because other formulas are returning error when reading from the second sheet.

Thanks in advance.

 

[Joe4]

Conditional Formatting does NOT change the values of entries. It simply changes their looks (how they are formatted).

If you want to trim values that are manually entered or pasted, you can do so with a Worksheet_Change event procedure, which runs whenever values are manually entered (via direct entry or copy/paste).

The code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = Trim(cell.Value)
    Next cell
    Application.EnableEvents = True
End Sub

You need to put this code in the Worksheet module that you want it applied to. To do that, simply right-click on the sheet tab name at the bottom of your code, select "View Code", and paste the code in the resulting VB Editor window.
This code will run automatically.

 

[Eamonn100]

Thanks for that but it seems to be the same as before with no change. I also did a little test with these names on a spare sheet with the same results . They have spaces leading and trailing.

Mary
Frank
Paul
John
Me
You

When I paste the code is there anything else I have to do, like hit enter? I've never used a code before.

 

[Joe4]

Note that this code runs when you manually enter the data on that sheet, and just on the cells you are adding it to.
So the code need to be put in the Worksheet module that you are pasting to, not the Sheet you are copying for.
It will also not run on pre-existing data, just new data being entered.

But it sounds to me like you are copying and pasting this data from somewhere else.
Is that correct?
If so, from where exactly?
Is what you are copying/pasting values or formulas?

It might be best if you walk us through the whole process you follow to enter this data.

 

[Eamonn100]

Yes, I export company info from the web. It's in a Excel formatted document. It's just values.

I download the sheet. Control and A copy the who thing onto a blank sheet on Excel. Then I have a second sheet with formulas to take what I need from the sheet. I'm using VLookup to find the name down Column A:A and then take what ever info I need from that row. Sometimes the names in Column A:A have trailing and leading spaces and they aren't fixed either. So using quotation marks isn't a fool proof way.

Edit.

Is there a way to use Vlookup that doesn't take into consideration the trailing and leading spaces? This might be simpler.

 

[Joe4]

Is there a way to use Vlookup that doesn't take into consideration the trailing and leading spaces? This might be simpler.

Are the spaces in the value you are looking up, or the range you are looking up into (in the data in the first argument, of the second argument of VLOOKUP)?
If the first, just use TRIM in your VLOOKUP formula, i.e.

[COLOR=#333333]=VLOOKUP(TRIM(A1),...)[/COLOR]

 

[Eamonn100]

Yes it first in the value. Sorry trim doesn't work. I put it in but got an error.

Here's a look at the formula. =VLOOKUP((A1),Company!$A:$DV,COUNTA(Company!26:26),0)

A1 finds the name in column A:A. Counta find the end of the row and I can work back from there with minus numbers, eg......-1,-2,-3.

 

[Joe4]

Let's confirm exactly what needs to be trimmed.
Is it column A on the same sheet where the VLOOKUP resides, or is it column A on the Company sheet?

 

[Eamonn100]

Sorry, it's column A in the company sheet.

 

[Joe4]

Well, that explains it then. Using the TRIM function on cell A1 on the sheet where the VLOOKUP resides will have no affect on that.

You need to run some clean-up code on column A of your Company sheet.
As long as they are normal spaces on your value and not special characters like non-breaking spaces, then the code I first posted should work for you, if you place it in the Company sheet module BEFORE you copy and paste the data to that tab.

Alternatively, we could manually call the code to run clean-up after the paste, and we could even limit it to column A.

If it is not working, we will need to determine what exactly is in there, as it might not be a normal space.