Hi Sherry,
I can't believe how helpful everyone is in this group
Just normal service !
I'm not exactly sure what you mean when
you say replace sOrig with my text and wonder if this would be
the cell column.
For use in the example as posted I meant first assign the text you want to
process to the variable sOrig. The variable would have been declared like
this
Dim sOrig as String
or it might be an argument in a function
purely for testing
copy "Doris Doe Smith" without the quotes into a cell, select the cell and
run this macro
Sub test1()
Dim s As String, sReturn As String
s = ActiveCell
sReturn = TrimSpaces(s)
MsgBox s & vbNewLine & sReturn
End Sub
Function TrimSpaces(sOrig As String) As String
Dim sTmp As String
Dim sOut As String
sTmp = Replace(sOrig, Chr(160), " ")
sOut = Application.WorksheetFunction.Trim(sTmp)
TrimSpaces = sOut
End Function
The above may not be efficient and not necessary to use the additional sTmp
variable. It depends on where your data (text to be processed) is coming
from and what you want to do with it. You might, for example, pass an array
to a function and loop each element. If it's a one off type of thing, copy
and paste all data to cells in a column, copy down the cell formula as
posted previously.
If you are sure the pseudo space chr(160) only crept into the examples in
your post, and never exists in your data, you'd only need the worksheet Trim
function.
If you want to filter or make further changes RegExp may will provide much
greater flexibility, otherwise go with the simplest and/or fastest method
you are sure will work for your needs.
Regards,
Peter T
"SherryScrapDog" wrote in message
...
Hi Peter,
Thanks for this response! I can't believe how helpful everyone is in this
group and I appreciate it so much. I'm not exactly sure what you mean
when
you say replace sOrig with my text and wonder if this would be the cell
column. However, the previous posts macro is working wonderful, and I'm
not
sure what this would do different than what I have now. I can see why you
said my examples threw you because as I look at them now, they do not look
like they have the 2 spaces in them as I had typed them. Please let me
know
if there is something this does that the other macro is not doing. And, I
assume this is a macro that you are giving me; let me know if it is
something
else. This is my first attempt at doing things programmatically in Excel,
so
I'm very ignorant and appreciate all the help I have received. Again,
Thanks! Sherry
"Peter T" wrote:
Worksheet & VB/VBA Trim functions are slightly different, inasmuch the
Worksheet function replaces any multiple spaces in the middle of text
with a
single space.
Your sample text as posted threw me at first (when pasted directly into
a
cell), but try this
sTmp = Replace(sOrig, Chr(160), " ")
sOut = Application.WorksheetFunction.Trim(sTmp)
where sOrig contains your text.
As a cell formula -
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))
Regards,
Peter T
"SherryScrapDog" wrote in
message
...
I just had some really great help with some Excel files from this
group
where
the formatting was done by various people over the last 15-20 years.
These
are names for genealogy, and I am loading many Excel files into Access
so
people can search by name and soundex. I don't know if the other
problem
I
am dealing with can be handled programatically, but some of the people
in
the
past entered extra spaces between the first and middle names. I am
going
thru the files manually now and removing the extra space. I have more
files
to do and thought it was worth asking if a macro could do this. Here
are
some examples:
John William (I change to John William by using delete key)
Doris Doe Smith (I change to Doris Doe Smith)
John W. ( change to John W.)
I found many posts on this site that talks about Trim, but could not
find
anything that specifically addressed just removing extra space within
text.
Thanks in advance if there is any help for me, Sherry