Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim does not work
I'm trying to create a macro to format a lot of data for a person who's not
very sophisticated with Excel. I've followed one of the suggestions on this post using msgbox asc(left(A5,1)) to find out that what appears to be "spaces" are not actually spaces but is chr(65). The earlier post also said that chr(160) is common. My question is: How do I detect what chr it is so that I can remove it. The Replace functions works to remove the chr only if I can identify what the characters are. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim does not work
Hi there Samantha,
Character 65 is "A". Is that not acceptable? If not, what *is* acceptable then? You can always check each cell value like so ... Sub StripChars() Dim rng As Range, i As Long For Each rng In Selection For i = Len(rng.Value) To 1 Step -1 Select Case Asc(Mid(rng.Value, i, 1)) Case 48 To 57, 65 To 90, 97 To 122 'numbers, upper case, lower case Case Else rng.Value = Replace(rng.Value, Mid(rng.Value, i, 1), "") End Select Next i Next rng End Sub This will take out everything except 0-9, A-Z and a-z. HTH Regards, Zack Barresse "Samantha" wrote in message ... I'm trying to create a macro to format a lot of data for a person who's not very sophisticated with Excel. I've followed one of the suggestions on this post using msgbox asc(left(A5,1)) to find out that what appears to be "spaces" are not actually spaces but is chr(65). The earlier post also said that chr(160) is common. My question is: How do I detect what chr it is so that I can remove it. The Replace functions works to remove the chr only if I can identify what the characters are. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim Function... does not work? | Excel Programming | |||
VBA Trim and Application.worksheetfunction.Trim | Excel Programming | |||
using trim cmd. When I type this command it does not work. When I. | Excel Worksheet Functions | |||
Trim function won't work in VBA | Excel Programming | |||
Trim function in Excel Doesn't work for certain cells | Excel Programming |