Consecutive delimiters are treated as a single delimiter. If not specified, it is assumed that words are separated by spaces. Delimiter – Use this argument to specify the word delimiter. ![]() Defaults to 1 - if the argument is omitted, the function will extract the first word.For instance, to extract the last word, input -1. Negative number – pulls a word from the end of the string.For example, to extract the first word, input 1 or leave empty. Positive number – pulls a word from the beginning of the string.Word_num – This argument specifies the ordinal number of the word you want to extract:.Data_range – This is the cell or range of cells from which you want to extract a word.Word_num = UBound(Split(text, Delimiter)) + (Word_num + 2)ĮxtractWord(data_range,, , ) Loop ' extract word containing a specific character by number If Word_num > a - 1 Or Word_num = -1 ThenĮnd If Else ' extract word If Word_num > UBound(Split(text, Delimiter)) + 1 Or UBound(Split(text, Delimiter)) = 0 ThenĮxtractWord = "" Else ' extract first word If Word_num = 0 ThenĮnd If ' extract last word If Word_num < 0 Then On Error Resume Next ' extract first word containing a specific character If Char "" Then Do While i 0 Then ' the default separator is a space If Delimiter = "" Or Delimiter = " " Thenĭelimiter = " " End If ' remove extra spaces Function ExtractWord(Data_range As Range, Optional Word_num As Long, Optional Delimiter As String, Optional Char As String) As String Dim rCell As Rangeĭim text As String, sWord As String Dim arr() As String, arr2(10) As String, i As Long, a As Long ' concatenate the range into a text string For Each rCell In Data_range
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |