Extracting Part Of A String

The functions, Left, Right, and Mid allow you to extract part of a cell’s entry.

If cell A1 contains “ABC567DEF, then

Left(Range(“A1”).Value, 3)

returns “ABC”

Mid(Range(“A1”).Value, 3)

returns C567DEF

Mid(Range(“A1”).Value, 4, 2)

returns just “56”, a text string. To convert it to a number use Val(any string).

Right(Range(“A1”).Value, 2)

returns “EF”

Mid() function

The Mid() function allows you to extract part of a string. Its arguments are

Mid(string, start position, Numbers of Characters)

The number of characters to extract are optional

For example, Mid(“ABCD”, 2, 2) returns “BC”. Mid(“ABCD”, 2) returns “BCD”.

You can use InStr in conjunction with Mid to find the location of a string within a string and

then extract just the needed text. For example:

Dim N As Integer

Dim lastName As String

Dim sName As String

sName = “John Smith”

N = InStr(“John Smith”, ” “)

lastName = Mid(sName, N, )

Please note that the InStr function is case sensitive unless you have put Option Compare Text

at the top of your module.

The following illustrates the use of the above functions:

Dim aStr As String

aStr = “ABCDE”

‘extract just AB

MsgBox Left(aStr, 2)

‘extract just DE

MsgBox Right(aStr, 2)

‘extract just CD (the string starting at position 3 that is 2 characters long)

MsgBox Mid(Astr, 3, 2)

‘extract all characters to the right, starting at character 2

MsgBox Mid(aStr, 2)

For more examples, highlight the word Mid, Left, or Right and press F1 while in a module.

Extracting Strings Separated By A /

This is a solution for a user who had a column of cells whose entries looked like the following:

asdf/qwer/zxcv/1234456567

The user wanted to extract each string or number and write it to the cells to the left of the entry,

splitting the up the value based on the “/”s and putting each value in a separate cell.

This is the routine that initiates the process. Select a range of cells and run it. Blank cells are

ignored.

Sub ProcessCells()

Dim cell As Range

For Each cell In Selection

‘call the routine that processes a cell if the entry is not blank

If Not IsEmpty(cell) Then _

ExtractValue cell

Next

End Sub

‘this routine is called by the above routine for nonblank

cells

Sub ExtractValue(anyCell As Range)

Dim s As String

Dim N As Integer, I As Integer

‘set a variable equal to the value in the cell

s = anyCell.Value

‘find the first occurrence of a / in the string

N = InStr(s, “/”)

‘if a / is found, process the string and loop until no more /’s

While N > 0

‘index the counter used to specify the offset for the output

I = I + 1

‘write the left portion excluding the / to the offset cell

anyCell.Offset(0, I).Value = Left(s, N 1)

‘remove the left portion and the / from the variable “s”

s = Mid(s, N + 1)

‘search for the next occurrence of a /

N = InStr(s, “/”)

Wend

‘index I for the next offset cell

I = I + 1

‘write the remaining string to this cell

anyCell.Offset(0, I).Value = s

End Sub

Finding The Number Of Occurrences Of A String In A Range

If you need to find the number of occurrences of a string in a range, there are several different

approaches you can use. If the string is the only entry in the cells, then the following would

work, if you wanted the count of occurrences of ABC filled cells in range A1:A10

Dim N As Long

N = Application.CountIf(Range(A1:A10)), “ABC”)

If the string can be embedded in a string, for example, “Part ABC”, then you must either check

each cell, or repeated use Find in a loop. The following illustrates using a Find loop:

Sub findABCs()

Dim R As Range, startCell As Range

Dim firstAddress As String

Dim foundCell As Range

Dim I As Long

‘set the range to search

Set R = Range(“A1:A10″)

‘set the cell to be used in the After argument

Set startCell = R.Cells(1)

Do

‘do the search and set a range variable to the result.

‘Search is by part instead of whole cell entry.

Set foundCell = R.Find(What:=”ABC”, _

After:=startCell, _

LookIn:=xlValues, LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False)

‘exit if no matching string found

If foundCell Is Nothing Then Exit Do

If I = 0 Then

‘the first time a match is found, store that cells address

firstAddress = foundCell.Address

Else

‘on future matches check the address, and exit if it is a repeat

‘of the first found cell, which indicates that the Find procedure

‘has looped back to the first cell.

If foundCell.Address = firstAddress Then Exit Do

End If

‘increase the count of found cells by one

I = I + 1

‘set the startCell to the foundCell so that the next search starts

‘after this cell, and does not continue to find the same cell

Set startCell = foundCell

Loop

‘display a message with the results

MsgBox I & ” found”

End Sub

Another way to check for the occurrence of a string within a string throughout a range is to check

cell by cell. This is less efficient than the above approach but simpler to write. The following

illustrates this approach.

Sub findABCsApproach2()

Dim R As Range, cell As Range

Dim I As Long

‘set the range to search

Set R = Range(“A1:A10”)

For Each cell In R

‘check each cell’s value and see if ABC is in it. Convert the

‘cell’s value to upper case so that the comparison is upper case

‘to upper case

If InStr(UCase(cell.Value), “ABC”) > 0 Then

‘increment the count by one when ABC is found.

I = I + 1

End If

Next

‘display a message with the results

MsgBox I & ” found”

End Sub

If you need to check entire rows or columns for a string, then you should restrict the range to be

searched to the sheet’s used range. This will make both approaches above run faster. The

following illustrates how you would do this:

‘set R initially to the overall range

Set R = Rows(“1:3)

‘redefine R to its intersection with the used range

Set R = Intersect(R, ActiveSheet.UsedRange)

How To Get The Number Of Characters In A Selection

The following code will return the number of characters, including blanks in a selection of cells:

Sub CharacterCount()

Dim cell As Range

Dim I As Integer

For Each cell In Selection

I = I + Len(cell.Value)

Next

MsgBox “there are ” & I & _

” characters and spaces in the selection”

End Sub

How To Test If A Cell Or Variable Contains A

Particular Text String

To test if a cell or a string variable contains a particular text string, use the InStr function. It

returns the starting position in a string of a string. If the string is not found a zero value is

returned.

You can do a case insensitivity test by setting the 1st and last arguments of InStr to 1. The 1st

argument is the starting position of the search and the last argument is a 1 for case insensitive

testing and a 0 for case sensitive comparisons. If one is supplied, then the other is required.

If InStr(1, Range(“A1”).Value, “ABC”, 1) > 0 Then

‘actions to take if ABC is in cell A1’s string

End If

Example:

Dim myString As String

myString = Ucase(Range(“A1”).Value)

If InStr( myString, “ABC”) > 0 Then

‘actions to take if ABC is in cell A1’s string

End If

Another way to do case insensitive tests using InStr is to put

Option Compare Text

at the top of your module. This makes all text comparisons case insensitive unless over ridden.

To override Option Compare Text, set the last argument of InStr to 0:

If InStr(1, Range(“A1”).Value, “ABC”, 0) > 0 Then

‘actions to take if ABC is in cell A1’s string

‘and ABC in the string is capitalized

End If

 

<————————————————————————————————————————–>

Paul Kelly sent me the next artikel.

In this item, we will look at a  really slick technique.
When you’re extracting data from a string in VBA, you normally use the Left, Right and Mid functions as shown in the code below
Sub ExtractString()

Dim s As String: s = “ABCD-7789.WXYZ”

Debug.Print Left(s, 2) ‘ Prints AB

Debug.Print Left(s, 4) ‘ Prints ABCD

Debug.Print Right(s, 2) ‘ Prints YZ

Debug.Print Right(s, 4) ‘ Prints WXYZ

Debug.Print Mid(s, 1, 2) ‘ Prints AB

Debug.Print Mid(s, 6, 4) ‘ Prints 7789

End Sub

These functions work great if the string is always the same length. However, if the string size varies, then using these functions can be very messy.
In the example below, we are reading the last name.
The name can be any length.
If we use Left, Right or Mid it can get complicated fast.
We need to search for the last comma and then do some calculation to extract the name correctly.
Sub GetLastName()

Dim s As String: s = “John,Henry,Smith”

Dim Position As Long, Length As Long

Position = InStrRev(s, “,”)

Length = Len(s)

‘ Prints Smith

Debug.Print Right(s, Length – Position)

‘ Alternative method. Prints Smith – do in one line

Debug.Print Right(s, Len(s) – InStrRev(s, “,”))

End Sub

If we want to read a name that is not first or last it gets even more complex.

However, by using the Split function we can easily get the names with much simpler code

Dim s As String: s = “John Henry Smith”

Debug.Print Split(s, ” “)(0) ‘ John

Debug.Print Split(s, ” “)(1) ‘ Henry

Debug.Print Split(s, ” “)(2) ‘ Smith

The Split function breaks a string into an array. Once it is an array, we can directly access any part.

With thanks to Paul Kelly

2 reacties