Hi all member!

I found this function to trim 2 spaces not neccessary in the string :

Example: TrimSpace(" This is new user ") ="This is new user"

How can i use this function to apply to the whole worksheet?



Function TrimSpace(strInput As String) As String
' This procedure trims extra space from any part of a string

Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long
If Trim(strInput) = "" Then Exit Function
' Split passed-in string.
astrInput = Split(Trim(strInput))

' Resize second array to be same size.
ReDim astrText(UBound(astrInput))

' Initialize counter variable for second array.

lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement) 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

' Join new array to return string.
TrimSpace = Join(astrText)
End Functio

--
thanhnguye
-----------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...fo&userid=3050
View this thread: http://www.excelforum.com/showthread.php?threadid=50154

You can add the following subroutine. It'll apply the formula to all used
cell which are found to be non-empty.

Sub apply_to_all_cells()
Dim cell As Object
With ActiveSheet.UsedRange
For Each cell In .Cells
If cell.Value < "" Then
cell.Value = TrimSpace(cell.Value)
End If
Next
End With
End Function


"thanhnguyen" wrote:


Hi all member!

I found this function to trim 2 spaces not neccessary in the string :

Example: TrimSpace(" This is new user ") ="This is new user"

How can i use this function to apply to the whole worksheet?



Function TrimSpace(strInput As String) As String
' This procedure trims extra space from any part of a string

Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long
If Trim(strInput) = "" Then Exit Function
' Split passed-in string.
astrInput = Split(Trim(strInput))

' Resize second array to be same size.
ReDim astrText(UBound(astrInput))

' Initialize counter variable for second array.

lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement) 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

' Join new array to return string.
TrimSpace = Join(astrText)
End Function


--
thanhnguyen

Hi Edwin Tam
Thanks for quickly reply, some question more please:

How can i apply that function to a range specified by user and I woul
like to trim some special characters at the begin position of th
string.

Ex: "1. System no1. This is new system" = "System no1. This is ne
system"

or
"II.( new name of system is Z)" ="new name of system is Z"

After I cut all space I would like to look up to database another valu
with input parameter is new string.

Ex :

This is example of my database which is created in Excel

Code Name Address
abc thanh nguyen London
xyz Jonh Paris
.... ....... ..............



When I use that function it automaticly display look-up value on othe
cell.
Ex: trimspace(" abc ") = "thanh nguyen"
or
trimspace("1. abc ") = "London

--
thanhnguye
-----------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...fo&userid=3050
View this thread: http://www.excelforum.com/showthread.php?threadid=50154

Hi all members!

Please help me to solve this problem!
Thanks in advanced!


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=501546

Hi

i think you've not given enough conditions.
you said "trim some special characters at the begin position of the
string", but what's some special chracters? and though you said
"at the begin position of the string", it seems to need to trim some
character at the end of string from your second example -
"II.( new name of system is Z)"
i wonder what's the exact conditions to trim a given string?

keizi

"thanhnguyen" wrote in
message ...

Hi Edwin Tam
Thanks for quickly reply, some question more please:

How can i apply that function to a range specified by user and I would
like to trim some special characters at the begin position of the
string.

Ex: "1. System no1. This is new system" = "System no1. This is new
system"

or
"II.( new name of system is Z)" ="new name of system is Z"

After I cut all space I would like to look up to database another value
with input parameter is new string.

Ex :

This is example of my database which is created in Excel

Code Name Address
abc thanh nguyen London
xyz Jonh Paris
... ....... ..............



When I use that function it automaticly display look-up value on other
cell.
Ex: trimspace(" abc ") = "thanh nguyen"
or
trimspace("1. abc ") = "London"


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=501546

Hi keizi and other members!

First I would like to say thanks so much for your kindly support

Now I can cut all the special characters which i want, but until now i
can not search into special range to select another data.

Please see example in my previous question for more detail.

A++
merci!


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=501546

sorry for not reading your post precisely.
if your trimspace already can get correct data, how about using vlookup function?

assuming the table below is in the range("a1:c10").

Code Name Address
abc thanh nguyen London
xyz Jonh Paris
... ....... ..............

in case of
Ex: trimspace(" abc ") = "thanh nguyen"

put in any cell
=VLOOKUP(trimspace(" abc ") ,range("a2:c10"),2,false)

in case of
or
trimspace("1. abc ") = "London"

=VLOOKUP(trimspace("1. abc ") ,range("a2:c10"),3,false)

Is this what you are asking for or am i still misreading your post?

keizi

"thanhnguyen" wrote in
message ...

Hi keizi and other members!

First I would like to say thanks so much for your kindly support

Now I can cut all the special characters which i want, but until now i
can not search into special range to select another data.

Please see example in my previous question for more detail.

A++
merci!


--
thanhnguyen
------------------------------------------------------------------------
thanhnguyen's Profile:
http://www.excelforum.com/member.php...o&userid=30502
View this thread: http://www.excelforum.com/showthread...hreadid=501546

typo:

put in any cell
=VLOOKUP(trimspace(" abc ") ,range("a2:c10"),2,false)

i mean
=VLOOKUP(trimspace(" abc "), A2:C10, 2, false)

in case of
or
trimspace("1. abc ") = "London"

=VLOOKUP(trimspace("1. abc ") ,range("a2:c10"),3,false)

and
=VLOOKUP(trimspace("1. abc "), A2:C10, 3, false)

also i'm expecting trimspace(" abc ") returns "abc" or
trimspace("1. abc ") returns "abc" respectively as you said.

if not, this would not work.

keizi

"kounoike" wrote in message
...
sorry for not reading your post precisely.
if your trimspace already can get correct data, how about using vlookup
function?