Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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. ## Re: Vlookup To Extract Text Before Period

Welcome to the forum....

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

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

3. I agreed to these rules
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. ## Re: Extract Text Before and After Period

Data>Text to columns...

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

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