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.

 

[Eamonn100]

The code still didn't work. Maybe I'll have to upload a copy of the sheet?

Anyway thanks for now but it's my bed time. I've been on the computer all day and need sleepy time. I'll reply again tomorrow.

Thanks for your help.

 

[Joe4]

Find any entry which has a leading space. Let's say it is in cell A10. Then put this formula and see what it returns:

=CODE(LEFT(A10,1))

It should return 32.
If it returns some other number, then you do not have a normal space, but some other special character in there.

 

[Eamonn100]

Ok rested and ready.

The names with a leading space (on the left) have returned 32. I also experimented and incerted a leading space and the number changed to 32.

Also, almost all values in column A have a trailing (on the right) space, (if this helps).

 

[Eamonn100]

Oh, I've worked out a work around.

I opened up a second company sheet and in column A used the formula =TRIM(CLEAN(Company!A1)). Though for some reason using that formula I could not format any of the results. So for all the other columns on Company2 sheet i just use =Company!A1.

Anyway, I'm still interested in what solution you can come up with as with everything in excel I learn something new.

 

[Joe4]

Based on everything that you have told me, the solutions that I gave you should work. I tested it out myself, and confirmed that it does.
If they do not, then I suspect you are not doing something correctly. Most likely, you are not putting the VBA code in the correct place. It needs to be put in the appropriate Sheet tab in VBA BEFORE you paste your data to that sheet.

So, if you were copying/pasting the data into Sheet2, you would need to first paste the VBA code that I gave you to the Sheet2 module in VBA before you copy and paste your data.

A few other things to confirm:
- The data you are copying/pasting is hard-coded data, right? They are not formulas that you are copying, are they?
- How exactly are you copying/pasting data to this sheet? What methodology are you using?

 

[Eamonn100]

Ok, that sounds about right. I've been putting the code into the wrong place. No worries.

 

[Joe4]

Yes, Event Procedures, which run automatically, have some strict requirements. They are not exactly like the regular Procedures and Functions that you create and place in Standard Modules.
They must:
- be named a certain way (no flexibility there)
- be placed in the proper module
If both those requirements are not met, they will not automatically fire.

Here is a good write-up on them: http://www.cpearson.com/excel/events.aspx

 

[Eamonn100]

Thanks for this. I think it's time to begin to learn about VBA,codes,macros and the like as I know nothing about them/it, (as you have seen).

A few links that you would recommend for VBA,code,macros for dummies would be appreciated.

 

[Joe4]

Chip Pearson's site is full of good information (the link I provided you earlier is from his site).

Another user has an extensive list of materials that should help. Check out this post here:
https://www.mrexcel.com/forum/gener...a-beginners-any-guiding-book.html#post4887353

 

[Eamonn100]

Ok great stuff, thanks.