using instr() to break up long strings of text

[davers5]

I have many lines of text that need to be broken up into pieces. Basically, the need to be broken up if there is more than one space between words. I was going to do this by searching for two spaces using the instr function and then looking for the next text character. Is there a way, using the instr function to find the first non-space character in a string? Maybe there's a wildcard or something? Thanks for the help,

Dave

 

[Russell Hauf]

On 2002-02-21 09:34, davers5 wrote:
I have many lines of text that need to be broken up into pieces. Basically, the need to be broken up if there is more than one space between words. I was going to do this by searching for two spaces using the instr function and then looking for the next text character. Is there a way, using the instr function to find the first non-space character in a string? Maybe there's a wildcard or something? Thanks for the help,

Dave

Will there only be 2 spaces between the words that need to be separated? Or could there also be 3 or more spaces?

 

[davers5]

There are two or more spaces between text characters that need to be seperated. Text segments with only one space between them need to stay together. Here is a sample:
"Account Type Claude Killey $65" would need to be split to "Account Type, Claude Killey, $65"

 

[Helen]

If all of the data you want on the rows are in roughly the same order in columns, then you could use the Data/Text to columns function.

Just an idea.

 

[davers5]

Well, that example didn't look right when it was posted to the board. There were a bunch of spaces (two or more) between the word "type" and "Claude" and a bunch of spaces between "Killey" and "$65".

 

[Russell Hauf]

On 2002-02-21 10:05, davers5 wrote:
There are two or more spaces between text characters that need to be seperated. Text segments with only one space between them need to stay together. Here is a sample:
"Account Type Claude Killey $65" would need to be split to "Account Type, Claude Killey, $65"

Here is a function that will split your string out for you (puts it into an array):

Option Explicit

Sub SplitOut()
    Dim strMain As String
    Dim astrSplit() As String
    Dim intI As Integer
    
    intI = 1
    ' Change the line below to point to your string.
    ' - you could also pass it in if you like.
    strMain = Range("A5")
    
    Do While InStr(Trim(strMain), "  ") > 0
        ReDim Preserve astrSplit(1 To intI)
        astrSplit(intI) = _
          Left(Trim(strMain), InStr(Trim(strMain), "  ") - 1)
        strMain = Mid(Trim(strMain), InStr(Trim(strMain), "  "))
        intI = intI + 1
    Loop

    ReDim Preserve astrSplit(1 To intI)
    astrSplit(intI) = Trim(strMain)

    For intI = 1 To UBound(astrSplit)
        Debug.Print astrSplit(intI)
    Next intI

End Sub

Hope this helps,

Russell

 

[davers5]

Thanks a lot Russell! I'll give it a shot.