Trim pace not necceesary

[thanhnguyen]

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

[Edwin Tam]

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

[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 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]

Hi all members!

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

[kounoike]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[thanhnguyen]

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!

[kounoike]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[kounoike]

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" <[email protected]> wrote in message
news:[email protected]...
> sorry for not reading your post precisely.
> if your trimspace already can get correct data, how about using vlookup
function?
>