none
VBA - Separate text in one cell to two cells RRS feed

  • Question

  • Hi,

    I have the following example:

    (the full sheet is much larger, this is just an example).

    I have a code that searches for specific texts in cells and returns the value after it. This value is then pasted somewhere else in the sheet. The snippet of this piece of code is:

    Dim UMS As Range Set wb = ThisWorkbook

    Set UMS = wb.Sheets(i).Cells.Find(What:="text1", LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) If UMS Is Nothing Then wb.Sheets(1).Cells(i, 1).Value = "Not Found" Else Row = UMS.Cells.Row Column = UMS.Cells.Column s = s & wb.Worksheets(i).Cells(Row, Column + 1).Text If s <> "" Then wb.Sheets(1).Rows(i).Columns(1).Value = Mid(s, 1) s = "" End If End If

    This returns:

    This is what I wanted, so this works fine, Option Explicit is also used. Now the problem: in some cases, the data is given in this format:

    (note that the word1 and word2 are only seperated by a blank).

    Searching for text1 isn't the problem, the problem is that it will return "word1 word2"(which is normal looking at the code). The number of characters of both words can change every time, but the blank to seperate the two words remains.

    Is there a way of still only retrieving word1? Is it possible to let is stop retrieving when the code finds a blank?

    I hope someone can help.

    Ganesh



    Sunday, March 25, 2018 12:24 AM

Answers

  • You can use the Split command to separate strings at specific characters. In your case a space is the delimiter.

    The Split command creates a zero based array of the values that are separated by the delimiter. Therefore the first element of the array is the first value.

    If there is only one word and no instances of the delimiter then only one element is assigned to the array.

    There is no need to find the row or column of the found data because it is all in the range variable. Use Offset to get the value from adjacent cells.

    Sub test()
        Dim wb As Workbook
        Dim i As Long
        Dim UMS As Range
        Dim arrSplit As Variant
       
        Set wb = ThisWorkbook

        i = 1      'One is assigned to i here because the code uses sheet i and also the output row is i.
       
        Set UMS = wb.Sheets(i).Cells.Find(What:="text1", LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)

        If UMS Is Nothing Then
            wb.Sheets(1).Cells(i, 1).Value = "Not Found"
           
            Else
           
            arrSplit = Split(UMS.Offset(0, 1).Value, " ")     'Use Offset for the adjacent cell
          
            wb.Sheets(1).Rows(i).Columns(1).Value = arrSplit(0)
       
        End If

    End Sub


    Regards, OssieMac

    • Marked as answer by ganeshgebhard Sunday, March 25, 2018 2:58 PM
    Sunday, March 25, 2018 1:44 AM