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

T

tocy777

New Member
Joined
Oct 28, 2015
Messages
33
So I use have this dataset:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name with Share & Value[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"] A.H. Belo Corp. Class A........................... 100,069 598,413[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 502"]
<tbody>[TR]
[TD="width: 502, align: left"] Aaron's, Inc...................................... 376,985 8,625,417[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"]# Abercrombie & Fitch Co. Class A................... 336,209 8,822,124[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"] AMC Entertainment Holdings, Inc. Class A.......... 10,048 219,046[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"] AMCON Distributing Co............................. 850 61,434[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"]#* America's Car-Mart, Inc........................... 58,111 1,363,284[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"]# American Eagle Outfitters, Inc.................... 604,752 8,853,569[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"]#* American Public Education, Inc.................... 93,145 1,469,828[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR="class: grid"]
[TD="width: 502, align: left"]#* Apollo Education Group, Inc....................... 267,612 2,124,839[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Share & Value[/TD]
[TD]Share[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]=TRIM(LEFT(SUBSTITUTE(A2,"..",REPT(" ",99)),99))[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99))[/TD]
[TD]=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",99)),99))[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))[/TD]
[/TR]
</tbody>[/TABLE]

To get these data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Share & Value[/TD]
[TD]Share[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD][TABLE="width: 273"]
<tbody>[TR="class: grid"]
[TD="width: 273, align: left"]#* 1-800-Flowers.com, Inc. Class A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR="class: grid"]
[TD="width: 141, align: left"]219,113 $ 2,180,174[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR="class: grid"]
[TD="width: 72, align: left"]219,113[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR="class: grid"]
[TD="width: 85, align: left"]2,180,174[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Sort by date Sort by votes
steve the fish said:
This is that formula in VBA:

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

Or using INSTR:

Code: 
x = Left(Range("A1"), InStr(Range("A1"), "..") - 1)
MsgBox x
Click to expand...


Ahhhhhhh...I get it now. I didn't know you could do that.
Thank you!
 
Upvote 0
But it is dumb to call an Excel worksheet function with the inherent overhead when there are perfectly good VBA alternatives.
 
Upvote 0
tocy777 said:
How would I proceed if I wanted to implement this function into a Sub? So it would automaticly give the value to the cells?
Click to expand...

Code: 
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
 
Upvote 0
steve the fish said:
We in England prefer not to be called dumb. Thanks.
Click to expand...

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

Code: 
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
 
Upvote 0
adkock said:
Code: 
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
Click to expand...

Thanks! I will try it out this one too!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Stop column data from moving
Replies
1
Views
896
kevindorward_dfsdanf
K
C
  • Question Question
Combining Multiple Individual Functions Into One
Replies
9
Views
1K
jtakw
jtakw
D
  • Question Question
Restricting an INDEX MATCH to a specific number of characters RIGHT from the last character
Replies
2
Views
717
kvsrinivasamurthy
K
M
  • Question Question
Help with Nested "AND" "OR" while using "LEFT, MID" functions
Replies
2
Views
482
markzasz
M
N
  • Question Question
Copy data from specific named worksheets and combine on a single worksheet
Replies
2
Views
601
NHagedorn
N

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top