VBA – 6. Strings

VBA Left, Mid, right functions

Dim str As String, strLeft As String, strRight As String, strMid As String
 
str = "James Bond"
 
strLeft = vba.Left(str, 7)
'returns "James B", which are the first 7 characters (space is counted as a distinct character).
MsgBox strLeft
 
strLeft = vba.Left(str, 15)
'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.
MsgBox strLeft
 
strRight = vba.Right(str, 7)
'returns "es Bond", which are the last 7 characters (space is counted as a distinct character).
MsgBox strRight
 
strRight = vba.Right(str, 15)
'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.
MsgBox strRight
 
strMid = vba.Mid(str, 2, 6)
'Returns "ames B". Starts from the second character ie. "a", and then specifies that 6 characters be returned starting from "a".
MsgBox strMid
 
strMid = vba.Mid(str, 2, 15)
'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the specified characters of 15 plus start number 2 (ie. total of 17) exceed the string length of 10 characters.
MsgBox strMid
 
strMid = vba.Mid(str, 2)
'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the second argument (char_numbers) is omitted.
MsgBox strMid
 
strMid = vba.Mid(str, 12, 2)
'Returns an empty string (zero length), because the start number of 12 exceeds the string length of 10 characters.
MsgBox strMid
 
'Returns 10, the string length measured by its number of characters.
MsgBox Len(str)
 
'Returns 10, the string length measured by its number of characters.
MsgBox Len("James Bond")

Remove the numeric character from the following and replace by the text data after removing numeric data.

str

Dim z As Integer
'
' For Each c In Range("a2:a4")
'
'
' For z = 1 To Len(c.Value)
'
' d = Mid(c.Value, z, 1)
'
' If not IsNumeric(d) Then
'
' result = result & Mid(c, z, 1)
'
'
' End If
'
'
' Next z
'

'' c.Value = result
' MsgBox result
' result = ""
'
' Next c

Remove the text character from the following and replace by the numeric data after removing text data.

str

Dim z As Integer
'
' For Each c In Range("a2:a4")
'
'
' For z = 1 To Len(c.Value)
'
' d = Mid(c.Value, z, 1)
'
' If IsNumeric(d) Then
'
' result = result & Mid(c, z, 1)
'
'
' End If
'
'
' Next z
'

'' c.Value = result
' MsgBox result
' result = ""
'
' Next c

remove the special characters from the following 

strings1

Private Sub CommandButton1_Click()
'

Dim r As Range

Dim vr As Variant

vr = Split("*,#,@,%", ",")

For Each r In Range("B2:B5")

Call formatd(r.Value, vr, r.Row)

Next r

‘create the follwing code in standard module

public bError as boolean 
Public Function formatd(str As String, ary As Variant, rw As Integer) As Boolean

Dim j As Integer

For j = LBound(ary) To UBound(ary)
 
 If VBA.InStr(str, ary(j)) Then
 
 bError = True
 Exit For
 
 End If
 
 Next j

 If bError = True Then
 
 Range("B" & rw).Value = Replace(str, ary(j), "")
 formatd = False
 bError = False
 
 Else
 
 formatd = True
 
 End If

Check if an email address is valid or not  sandeep@opensolutionscom

sub s1()

dim str as string

str=range("A2").value  'email address which needs to be checked

if valid_email(str)=true then

msgbox "valid"

else 

msgbox "invalid address"

end sub


Public Function Valid_Email_ID(E_mail_ID As String) As Boolean

Instr(start, “string”, “search”)
 
 If VBA.InStr(E_mail_ID, "Opensolutions.com") = 0 And VBA.InStr(E_mail_ID, "opensolutions.com") = 0 Then
 MsgBox "Please check" & vbNewLine & _
 "Domain Name Incorrect/not present in Email ID", vbCritical, SetTitle
 Valid_Email_ID = False
 Exit Function
 End If
 
 If VBA.InStr(E_mail_ID, "@") = 0 Or VBA.InStr(E_mail_ID, " ") > 0 Then
 MsgBox "Please specify a valid e-mail address!", vbCritical, SetTitle
 Valid_Email_ID = False
 Exit Functiond
 End If
 
 If VBA.StrComp(Range(“h2”).value, Range(“h3”).value, vbTextCompare) Then
 MsgBox "Please specify a valid e-mail address!", vbCritical, SetTitle
 Valid_Email_ID = False
 Exit Function
 End If
 Valid_Email_ID = True
End Function

Check if the first character ins range starts from 1 , 2 o3 

dim c as range

dim chk as string

for each c in range(“A2:A5”)

if isnumeric(c.value) then

select case left(c.value,1)

case 1

msgbox “starts with 1”

case 2

msgbox “starts with 2”

case else

msbgox “na”

End if

next c

 

get the first middle and last name from a string

string=Narendra Damodardas Modi

Dim s As String
s = “Narendra Damodardas Modi”

Dim nm As Variant

nm = Split(s, ” “)  ‘ split by space

Dim j As Integer

For j = LBound(nm) To UBound(nm)

MsgBox nm(j)

Next j

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply