need a macro to find spaces in a string (1 Viewer)

smiler44

Registered User.
Local time
Today, 22:29
Joined
Jul 15, 2008
Messages
641
Can you help please?
I need a macro to find what is to the left of the first space, to the left of the second space etc of a string and the location of each space in a string, there are 4 spaces in the string.

I have searched the internet but all I can find is formulas

thank you

smiler44
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:29
Joined
May 21, 2018
Messages
8,463
I think you will need VBA, but I do not use macros and most people here do not. If you want vba
Code:
Public Function GetItemFromString(strInput As String, ItemNumber As Integer) As String
  Dim aStr() As String
  aStr = Split(strInput, " ")
  If UBound(aStr) >= (ItemNumber - 1) Then GetItemFromString = Trim(aStr(ItemNumber - 1))
End Function

Public Sub testIt()
  Dim x As String
  x = "this is a string with spaces"
  Debug.Print GetItemFromString(x, 1) 'prints "this"
  Debug.Print GetItemFromString(x, 2) ' prints is
  Debug.Print GetItemFromString(x, 100) ' prints nothing
End Sub
 

smiler44

Registered User.
Local time
Today, 22:29
Joined
Jul 15, 2008
Messages
641
thank you Majp I'll try your code as its less then what I have just come up with which is not very pretty

Code:
Sub macro5()
'find the location of the first 5 spaces
Dim SearchString ' what to search
Dim SearchChar ' charector to find
Dim MyPos1 ' position of cahrector to find
Dim mypos2
Dim mypos3
Dim mypos4
Dim mypos5
Dim b 'used to find second space mypos2
Dim c 'used to find third space mypos3
Dim d
Dim e
SearchString = Range("b2")
SearchChar = " "
        
MyPos1 = InStr(1, SearchString, SearchChar, 1)
MsgBox MyPos1
''''''''''''''''''''''''''''''''
b = MyPos1 + 1
mypos2 = InStr(b, SearchString, SearchChar, 1)
MsgBox mypos2
'''''''''''''''''''''''''''''''''''''''''''''''
c = mypos2 + 1
mypos3 = InStr(c, SearchString, SearchChar, 1)
MsgBox mypos3
'''''''''''''''''''''''''''''''''''''''''''''''''''
d = mypos3 + 1
mypos4 = InStr(d, SearchString, SearchChar, 1)
MsgBox mypos4
'''''''''''''''''''''''''''''''''''''''''''''''''
e = mypos4 + 1
mypos5 = InStr(e, SearchString, SearchChar, 1)
MsgBox mypos5
 
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:29
Joined
May 21, 2018
Messages
8,463
I noticed in your code you made this a variable
Dim SearchChar ' charector to find

If you want to be able to have any delimeter. then modify the code with an optional parameter
Code:
Public Function GetItemFromString(strInput As String, ItemNumber As Integer, Optional Delimeter As String = " ") As String
  Dim aStr() As String
  aStr = Split(strInput, Delimeter)
  If UBound(aStr) >= (ItemNumber - 1) Then GetItemFromString = Trim(aStr(ItemNumber - 1))
End Function

So this works as well
Code:
Public Sub testIt()
  Dim x As String
  x = "this is a string with spaces"
  Debug.Print GetItemFromString(x, 1) 'prints "this"
  Debug.Print GetItemFromString(x, 2) ' prints is
  Debug.Print GetItemFromString(x, 100) ' prints nothing
  
  x = "this!is!a!string!delimited!withExclamation"
  Debug.Print GetItemFromString(x, 4, "!") 'returns string
End Sub
 

Users who are viewing this thread

Top Bottom