trim
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 |
trim
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 |
trim
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 |
trim
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 |
trim
Thankyou all for your replys, this has helped alot. I went with
letapia, but they are all simular anyway. Thanks again. Regards Robert |
All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com