Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-15-2019, 12:10 PM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
need a macro to find spaces in a string

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

smiler44 is offline   Reply With Quote
Old 03-15-2019, 12:29 PM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,567
Thanks: 25
Thanked 471 Times in 447 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: need a macro to find spaces in a string

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
MajP is offline   Reply With Quote
Old 03-15-2019, 12:51 PM   #3
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: need a macro to find spaces in a string

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

smiler44 is offline   Reply With Quote
Old 03-15-2019, 01:06 PM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,567
Thanks: 25
Thanked 471 Times in 447 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: need a macro to find spaces in a string

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

MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Blanks Spaces in Field using FIND Keyword JohnLee Queries 14 03-31-2010 02:24 AM
find all commas or all spaces in a string lala Forms 2 08-29-2008 08:09 AM
Add Spaces to String rtdc Modules & VBA 7 09-27-2007 07:15 AM
Spaces in string IanT Queries 2 08-08-2005 12:54 PM
spaces in a string razorking Queries 3 02-05-2005 01:09 AM




All times are GMT -8. The time now is 04:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World