How to use MID,LEFT,RIGHT functions in VBA to read data from a specific character?

[tocy777]

So I use have this dataset:

Name with Share & Value

A.H. Belo Corp. Class A........................... 100,069 598,413 <tbody> </tbody>

Aaron's, Inc...................................... 376,985 8,625,417 <tbody> </tbody>

# Abercrombie & Fitch Co. Class A................... 336,209 8,822,124 <tbody> </tbody>

AMC Entertainment Holdings, Inc. Class A.......... 10,048 219,046 <tbody> </tbody>

AMCON Distributing Co............................. 850 61,434 <tbody> </tbody>

#* America's Car-Mart, Inc........................... 58,111 1,363,284 <tbody> </tbody>

# American Eagle Outfitters, Inc.................... 604,752 8,853,569 <tbody> </tbody>

#* American Public Education, Inc.................... 93,145 1,469,828 <tbody> </tbody>

#* Apollo Education Group, Inc....................... 267,612 2,124,839 <tbody> </tbody>

<tbody>
</tbody>

So I have to divide this data into three columns: Name, Share and Value
I used the following functions to get the data:

Name	Share & Value	Share	Value
=TRIM(LEFT(SUBSTITUTE(A2,"..",REPT(" ",99)),99))	=TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99))	=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",99)),99))	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

<tbody>
</tbody>

To get these data:

Name	Share & Value	Share	Value
#* 1-800-Flowers.com, Inc. Class A <tbody> </tbody>	219,113 $ 2,180,174 <tbody> </tbody>	219,113 <tbody> </tbody>	2,180,174 <tbody> </tbody>

<tbody>
</tbody>

But as far as I have googled there is not an option in VBA to use the LEFT or RIGHT function to read data eg.: after the first '..' happens from the right side.
Is there any way it could be done?
If you have questions let me know I will elaborate.

 

[theBardd]

There is, with Instr

Dim this As Worksheet
Dim that As Worksheet
Dim doubledot As Long
Dim firstspace As Long
Dim lastspace As Long
Dim lencell As Long
Dim lastrow As Long
Dim i As Long

    Set this = ActiveSheet
    Set that = Worksheets("Results")
    With this
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastrow
        
           lencell = Len(.Cells(i, "A").Value)
           doubledot = InStr(.Cells(i, "A").Value, "..")
           firstspace = InStr(doubledot, .Cells(i, "A").Value, " ")
            lastspace = InStrRev(.Cells(i, "A").Value, " ")
           that.Cells(i, "A").Value = Left$(.Cells(i, "A").Value, doubledot - 1)
           that.Cells(i, "B").Value = Mid$(.Cells(i, "A").Value, firstspace + 1, lastspace - firstspace)
           that.Cells(i, "C").Value = Right$(.Cells(i, "A").Value, lencell - lastspace)
        Next i
    End With

 

[adkock]

Have you considered using split?

Option Explicit

Function GetName(FullText As String, mySep As String) As String
Dim SplitText() As String
SplitText = Split(FullText, mySep)
GetName = SplitText(0)
End Function

Function GetShare(FullText As String, mySep As String, myPartialSep As String, mySep2 As String)
Dim SplitText() As String
Dim SplitText2() As String
Dim ShareValue As String

SplitText = Split(FullText, mySep)
SplitText2 = Split(SplitText(UBound(SplitText)), mySep2)
ShareValue = SplitText2(1)

If Left(ShareValue, Len(myPartialSep)) = myPartialSep Then
   GetShare = Right(ShareValue, Len(ShareValue) - Len(myPartialSep))
Else
   GetShare = ShareValue
End If
End Function

Function GetValue(FullText As String, mySep As String, mySep2 As String)
Dim SplitText() As String
Dim SplitText2() As String

SplitText = Split(FullText, mySep)
SplitText2 = Split(SplitText(UBound(SplitText)), mySep2)

GetValue = SplitText2(UBound(SplitText2))
End Function

Now, if name / share / value are in field A1, you could enter the following formulas in respectively B1, C1, D1 and E1

B1: =getname(A1,"..")
C1: = D1 & " " & E1
D1: =GetShare(A1,"..","."," ")
E1: =GetValue(A1,".."," ")

 

[steve the fish]

Left is a valid VBA function.

 

[tocy777]

There is, with Instr

Dim this As Worksheet
Dim that As Worksheet
Dim doubledot As Long
Dim firstspace As Long
Dim lastspace As Long
Dim lencell As Long
Dim lastrow As Long
Dim i As Long

    Set this = ActiveSheet
    Set that = Worksheets("Results")
    With this
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastrow
        
           lencell = Len(.Cells(i, "A").Value)
           doubledot = InStr(.Cells(i, "A").Value, "..")
           firstspace = InStr(doubledot, .Cells(i, "A").Value, " ")
            lastspace = InStrRev(.Cells(i, "A").Value, " ")
           that.Cells(i, "A").Value = Left$(.Cells(i, "A").Value, doubledot - 1)
           that.Cells(i, "B").Value = Mid$(.Cells(i, "A").Value, firstspace + 1, lastspace - firstspace)
           that.Cells(i, "C").Value = Right$(.Cells(i, "A").Value, lencell - lastspace)
        Next i
    End With

It stops at:

Set that = Worksheets("Results")

 

[tocy777]

Have you considered using split?

Option Explicit

Function GetName(FullText As String, mySep As String) As String
Dim SplitText() As String
SplitText = Split(FullText, mySep)
GetName = SplitText(0)
End Function

Function GetShare(FullText As String, mySep As String, myPartialSep As String, mySep2 As String)
Dim SplitText() As String
Dim SplitText2() As String
Dim ShareValue As String

SplitText = Split(FullText, mySep)
SplitText2 = Split(SplitText(UBound(SplitText)), mySep2)
ShareValue = SplitText2(1)

If Left(ShareValue, Len(myPartialSep)) = myPartialSep Then
   GetShare = Right(ShareValue, Len(ShareValue) - Len(myPartialSep))
Else
   GetShare = ShareValue
End If
End Function

Function GetValue(FullText As String, mySep As String, mySep2 As String)
Dim SplitText() As String
Dim SplitText2() As String

SplitText = Split(FullText, mySep)
SplitText2 = Split(SplitText(UBound(SplitText)), mySep2)

GetValue = SplitText2(UBound(SplitText2))
End Function

Now, if name / share / value are in field A1, you could enter the following formulas in respectively B1, C1, D1 and E1

B1: =getname(A1,"..")
C1: = D1 & " " & E1
D1: =GetShare(A1,"..","."," ")
E1: =GetValue(A1,".."," ")

How would I proceed if I wanted to implement this function into a Sub? So it would automaticly give the value to the cells?

 

[tocy777]

Left is a valid VBA function.

I never argued that.

 

[steve the fish]

I never argued that.

So what does this mean then?

But as far as I have googled there is not an option in VBA to use the LEFT or RIGHT function to read data eg.: after the first '..' happens from the right side.

 

[tocy777]

So what does this mean then?

But as far as I have googled there is not an option in VBA to use the LEFT or RIGHT function to read data eg.: after the first '..' happens from the right side.

That in VBA you can't add a character as a reference point in a string.
I can use left, but I can't use left from the first occurrence of ".."
In the LEFT formula I can.
Like this: =TRIM(LEFT(SUBSTITUTE(A2,"..",REPT(" ",99)),99))

 

[steve the fish]

This is that formula in VBA:

x = WorksheetFunction.Trim(Left(WorksheetFunction.Substitute(Range("A2"), "..", WorksheetFunction.Rept(" ", 99)), 99))
MsgBox x

Or using INSTR:

x = Left(Range("A1"), InStr(Range("A1"), "..") - 1)
MsgBox x