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 Functio

 

[Guest]

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]

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

 

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

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

 

[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

=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