Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Extract Text Before/After Period (Full Stop)

  1. #1
    Join Date
    30th May 2008
    Posts
    1

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,543

    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
    _______________________________________________
    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

    _______________________________________________

  3. #3
    Join Date
    30th May 2008
    Posts
    1

    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:

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

    VB:
    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 
    
    
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

    Re: Extract Text Before and After Period

    Data>Text to columns...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Stop Macro Running After Specfied Time Period
    By Jesse.Law in forum EXCEL HELP
    Replies: 6
    Last Post: October 18th, 2007, 09:36
  2. Replace Full Text With Abbreviation
    By Sherri in forum EXCEL HELP
    Replies: 6
    Last Post: December 22nd, 2006, 15:53
  3. Replies: 4
    Last Post: October 18th, 2006, 12:21
  4. Indentify/Locate Text Ending with Full Stop.
    By Ken MacPherson in forum EXCEL HELP
    Replies: 2
    Last Post: July 22nd, 2006, 17:07

Bookmarks

Posting Permissions

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