Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Extract Text Before/After Period (Full Stop)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract Text Before/After Period (Full Stop)

    I have a series of text - domains actually (i.e. goldintre.cr.usgs.gov.) - and I need to extract the text that appears before the first period in one cell, and the text that appears after the first period. Both have to be without the periods. So in the above example the result should be 'goldintre' in one cell and 'cs.usgs.gov' on the other.

  • #2
    Re: Vlookup To Extract Text Before Period

    Welcome to the forum....

    What does VLOOKUP have to do with the problem or solution?

    Never assume a solution in your thread title... just state your problem.

    I have updated your thread title for you.

    Assuming your original text is in A1...
    In B1 enter:
    =LEFT(A1,FIND(".",A1)-1)

    And in C1 enter:
    =MID(A1,FIND(".",A1)+1,256)

    Ger

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: Extract Text Before and After Period

      Hi there,

      I've written a few little bits recently to find strings within stuff, the first one you might need is the following:

      Code:
      Function findN(text As Variant, n As Integer, delimiter As String) As Integer   
      'Gives the position of the nth delimiter
      
      Dim found As Integer
      Dim place As Integer
      
      found = 0
      place = 0
      
      For i = 1 To n + 1 ' Add 1 as we start at 1 not zero
              place = found 'this will be 0 the first time round
              found = InStr(found + 1, text, delimiter)
      Next i
      
      findN = place
      End Function
      There are also 2 split variants I've written, which might help you more instantly:

      Code:
      Function BeforeSplit(text As Variant, n As Integer, delimiter As String)
      '' Finds the Substring before the nth delimiter.
      subStrings = Split(text, delimiter)
      
      If UBound(subStrings) > n - 1 Then
          BeforeSplit = Trim(subStrings(n - 1))
      Else
          BeforeSplit = ""
      End If
      
      End Function
      Code:
      Function AfterSplit(text As Variant, n As Integer, delimiter As String)
      '' Finds the Substring after the nth delimiter.
      subStrings = Split(text, delimiter)
      
      If UBound(subStrings) >= n Then
          AfterSplit = Trim(subStrings(n))
      Else
          AfterSplit = ""
      End If
      
      End Function
      Usage: strMyVar = BeforeSplit(myRange.value, myDelimiterposition, ".")

      Hope that helps!

      Paul.

      Comment


      • #4
        Re: Extract Text Before and After Period

        Data>Text to columns...

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X