Hi, could somebody advice on how to trim using vba. Im trimming data
like;
"21 22 3 4" or "20 2 22 3". All I need from these are the firs two
numbers so for these two It would be "21 22" & "20 2". Ive tried
x = ActiveCell.Value
y = Left(x, 2)
z = Left(x, 5) y to get the first number then I was going to use z to
get the first two numbers and then 'right' to get the second number.
Then I realised Id have a problem with single digit numbers. Now I
thinking is there a away of getting the numbers using the spaces as a
reference. x = first number before first space, z = second number
between first and second space.
Regards Robert

try:

Sub xx()
Dim a As Variant
a = "21 22 3 4"
a = Application.Trim(a)
a = Split(a, " ") ' Is in option base 0
ReDim Preserve a(1) ' gets 2 elements
a = Join(a, " ")
Debug.Print a
End Sub

You'd need to adapt this; change it to a function and pass 'a' as an argument.
" wrote:

Hi, could somebody advice on how to trim using vba. Im trimming data
like;
"21 22 3 4" or "20 2 22 3". All I need from these are the firs two
numbers so for these two It would be "21 22" & "20 2". Ive tried
x = ActiveCell.Value
y = Left(x, 2)
z = Left(x, 5) y to get the first number then I was going to use z to
get the first two numbers and then 'right' to get the second number.
Then I realised Id have a problem with single digit numbers. Now I
thinking is there a away of getting the numbers using the spaces as a
reference. x = first number before first space, z = second number
between first and second space.
Regards Robert

see if it helps ...

Function GetFirstNumber(myText As String) As String
Dim iPos As Integer
iPos = InStr(1, myText, " ")
GetFirstNumber = Left(myText, iPos - 1)
End Function

Sub Test()
Dim FirstNumber As String, SecondNumber As String
Dim TestString As String

TestString = "21 22 3 4"
FirstNumber = GetFirstNumber(TestString)
SecondNumber = GetFirstNumber(Mid(TestString, Len(FirstNumber) + 2,
Len(TestString)))
Debug.Print FirstNumber & Chr(13)
Debug.Print SecondNumber

End Sub

One way:

Public Function TrimAtSecondSpace(ByVal sString As String) As String
On Error Resume Next
TrimAtSecondSpace = Left(sString, InStr( _
InStr(1, sString, " ") + 1, sString, " ") - 1)
End Function


In article om,
wrote:

Hi, could somebody advice on how to trim using vba. Im trimming data
like;
"21 22 3 4" or "20 2 22 3". All I need from these are the firs two
numbers so for these two It would be "21 22" & "20 2". Ive tried
x = ActiveCell.Value
y = Left(x, 2)
z = Left(x, 5) y to get the first number then I was going to use z to
get the first two numbers and then 'right' to get the second number.
Then I realised Id have a problem with single digit numbers. Now I
thinking is there a away of getting the numbers using the spaces as a
reference. x = first number before first space, z = second number
between first and second space.
Regards Robert

Thankyou all for your replys, this has helped alot. I went with
letapia, but they are all simular anyway. Thanks again.
Regards Robert