What character is that?

[kin]

Hi

do you know any function that recognises which character is in a particular cell?

thanks

 

[Peter_SSs]

Hi

do you know any function that recognises which character is in a particular cell?

thanks

See if the CODE() function is what you want.

 

[kin]

yes it is 160

how can i use this

Sub test()
With Range("a1:az1000")
.Value = [if(a1:az1000<>"",trim(a1:az1000),"")]
With .Characters(1, 18).Font
.Name = "Arial"
.FontStyle = "?ioiia"
.Size = 10
.ColorIndex = 1
End With
End With
End Sub


to get rid of 160 in a sheet?

 

[nbrcrunch]

trim removes leading and trailing spaces. look at the CLEAN() function.

Sub Macro1()
With Selection
For Each cell In Selection
cell.Value = WorksheetFunction.Clean(cell.Value)
Next
End With
End Sub

btw, with the above. It will work on whichever cell or range of cells are selected. No need to pre-declare what the range is. So select the range, run the macro and BINGO.

 

[nbrcrunch]

btw, the downside of the above sledgehammer approach is that if the errant characters acted as spaces, you'll find your words run together at those points. In that case, the SUBSTITUTE() function would probably provide a bit more finesse. and if you used Alt-Enter to put in a carriage return in the cell, that will also be removed, causing the two words on either side of it to become one.

 

[pgc01]

Hi nbrcrunch

Did you try the code?

The character 160 is the HTML non-breaking space. Being a space I think it will not be "cleaned".

to get rid of 160 in a sheet?

Kin

Can't you just replace it with an empty string?

 

[kin]

the code says type mismatch in

For Each cell In Selection

 

[nbrcrunch]

not only did I try the code, but i ran it several times flawlessly. odd.

 

[pgc01]

Odd indeed.

This is what I did:

- in A1: ="Ab"&CHAR(160)&"cd"
- copy, paste special values to A2

Ran the code:

Sub RmNBSP()
Dim r As Range
 
For Each r In Range("A1:A2")
    r = WorksheetFunction.Clean(r)
Next r
End Sub

I tried it in excel 2000 and excel 2007 and in both case the code did not delete the nbsp.

Can you tell me what is your version of excel?

 

[nbrcrunch]

but you used all valid alpha characters.

what i did was...

type: This is a test.

Between "a" and "test" I entered a Alt-Enter. When wrap it on, "test" will wrap to the next line. When wrap is off, a box-like character will show between the two words. Either way, the macro will strip it off.

The macro is especially useful when getting dumps from certain databases.