Excel formula that spots space as last character

[LJREdinburgh]

Hi All,

I've got a huge dataset that I need to export out of Excel into a statistical analysis programme. Annoyingly, the stats programme can't deal with the dataset if one of the cells contains a space as a last character (eg, "12345 " instead of "12345".)

Is there a way to highlight cells that contain a space as a last character (eg, through conditional formatting or any formula)?

Thank you for your thoughts,

LJ

 

[JackDanIce]

Welcome to the board.

For conditional formatting you could use the formula:

=RIGHT(A1,1)=" "

Which will evaluate to either TRUE or FALSE and apply the conditional formatting if true.

 

[shadow12345]

Hi,
Welcome to the board... are you looking to just highlight it, or do you want to remove it if its a space? seems like removing it is what you want to do if it breaks the stats program.


If you do want to remove them.... this would take them out of the selected cells (if you need a range then change the in selection.cells part )

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Trim(c)
Next
End Sub

 

[Rick Rothstein]

If you do want to remove them.... this would take them out of the selected cells (if you need a range then change the in selection.cells part )

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Trim(c)
Next
End Sub

Or without using a loop...

Sub NoSpaces()
  Selection = Evaluate("IF(ROW(),TRIM(" & Selection.Address & "))")
End Sub

 

[shadow12345]

Or without using a loop...

Sub NoSpaces()
  Selection = Evaluate("IF(ROW(),TRIM(" & Selection.Address & "))")
End Sub

and now if off to read about evaluate ... do love to find new stuff (although this seems like one i should of seen before)

 

[LJREdinburgh]

Stunning replies!

Shadow12345's Code is working perfectly. I have tried it on a small data set around 150 entries, felt like it took a while, so am wondering how it will hold up with around 15,000 entries. Will let you know!

Rick, can you explain what the "loop" is?

JackDanIce, thanks for the formula, will be of use for other purposes.

Cheers