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.

 

[tocy777]

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

Ahhhhhhh...I get it now. I didn't know you could do that.
Thank you!

 

[theBardd]

But it is dumb to call an Excel worksheet function with the inherent overhead when there are perfectly good VBA alternatives.

 

[theBardd]

It stops at:

Set that = Worksheets("Results")

You have to do some of the work yourself. Results is the name of the output sheet I used, you need to adjust to your situation.

 

[steve the fish]

We in England prefer not to be called dumb. Thanks.

 

[adkock]

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

Option Explicit

Sub SplitMyStrings()
Dim DataCol As String: DataCol = "A"
Dim NameCol As String: NameCol = "B"
Dim ShareValueCol As String: ShareValueCol = "C"
Dim ShareCol As String: ShareCol = "D"
Dim ValueCol As String: ValueCol = "E"
Dim mySep As String: mySep = ".."
Dim myPartialSep As String: myPartialSep = "."
Dim mySep2 As String: mySep2 = " "
Dim myText As String
Dim i As Integer: i = 1

Do While ActiveSheet.Range(DataCol & i) <> ""
   myText = ActiveSheet.Range(DataCol & i)
   Range(NameCol & i) = GetName(myText, mySep)
   Range(ShareValueCol & i) = GetShare(myText, mySep, myPartialSep, mySep2) & " " & GetValue(myText, mySep, mySep2)
   Range(ShareCol & i) = GetShare(myText, mySep, myPartialSep, mySep2)
   Range(ValueCol & i) = GetValue(myText, mySep, mySep2)
   i = i + 1
   Loop
End Sub


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

 

[tocy777]

We in England prefer not to be called dumb. Thanks.

It worked like a charm. Just for fun this the code I made:

Dim i As Long


For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
  Range("E" & i) = WorksheetFunction.Trim(Left(WorksheetFunction.Substitute(Range("A" & i), "..", WorksheetFunction.Rept(" ", 99)), 99))
Next i


For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
  Range("B" & i) = WorksheetFunction.Trim(Right(WorksheetFunction.Substitute(Range("A" & i), ".", WorksheetFunction.Rept(" ", 99)), 99))
Next i


For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
  Range("C" & i) = WorksheetFunction.Trim(Left(WorksheetFunction.Substitute(Range("B" & i), " ", WorksheetFunction.Rept(" ", 99)), 99))
Next i


For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
  Range("D" & i) = WorksheetFunction.Trim(Right(WorksheetFunction.Substitute(Range("B" & i), " ", WorksheetFunction.Rept(" ", 99)), 99))
Next i

 

[tocy777]

Option Explicit

Sub SplitMyStrings()
Dim DataCol As String: DataCol = "A"
Dim NameCol As String: NameCol = "B"
Dim ShareValueCol As String: ShareValueCol = "C"
Dim ShareCol As String: ShareCol = "D"
Dim ValueCol As String: ValueCol = "E"
Dim mySep As String: mySep = ".."
Dim myPartialSep As String: myPartialSep = "."
Dim mySep2 As String: mySep2 = " "
Dim myText As String
Dim i As Integer: i = 1

Do While ActiveSheet.Range(DataCol & i) <> ""
   myText = ActiveSheet.Range(DataCol & i)
   Range(NameCol & i) = GetName(myText, mySep)
   Range(ShareValueCol & i) = GetShare(myText, mySep, myPartialSep, mySep2) & " " & GetValue(myText, mySep, mySep2)
   Range(ShareCol & i) = GetShare(myText, mySep, myPartialSep, mySep2)
   Range(ValueCol & i) = GetValue(myText, mySep, mySep2)
   i = i + 1
   Loop
End Sub


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

Thanks! I will try it out this one too!