using instr() to break up long strings of text
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: using instr() to break up long strings of text

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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".

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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):

    Code:
    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com