University of Auburn Code

In VBA use InStrRev (or 188007 - HOWTO: Simulate Visual Basic 6.0 String Functions in VB5)

MsgBox Mid("blah\blah1\blah2\blah3", InStrRev("blah\blah1\blah2\blah3", "\") + 1)

Find the number of words in a string (#wordcnt)

A worksheet solution: returns the number of words found in cell A1, where CHAR(32) is a space. The char(32) is same as a hex code of 20 to some of us, and is less ambiguous than " ", and less likely to be inadvertently modified.

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),CHAR(32),""))+1)

The above reduces the need for the following which probably can be greatly reduced based on the technique used above.

Peo Sjoblom simplifies coding to (but this is not quite it because this is incorrect):

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("open"),"")))/LEN("open")

WordCount returns 6, which is the number of words in the following example: in the string. The string uses a space(" ") as the separator in the following example:

=WordCount("There are six words found here."," ")

Code for WordCount() (This code is derived from ExtractElement coding)

Find the first n words in a string (#1stNwords)

Find the first two words in a string. The TRIM eliminates leading blanks if any.

=LEFT(TRIM(A1),(FIND(" ",TRIM(A1)&" ",FIND(" ",TRIM(A1)&" ")+1)-1))

For what it's worth, there is an LTRIM in VBA, but not in Excel. Excel will reduce the number of spaces in the middle, so that "A TWO" would still produce first two words of "A TWO"

Find the nth occurrence of a word in a string, etc. (#nthoccurred)

No coding yet -- haven't seen use for it yet.

Find the number of occurrences of a string in a single cell (#occurs)

=(LEN(A1)-LEN(SUBSTITUTE(A1,"aa","")))/LEN("aa")

-- Jim Rech in worksheet.functions 1999-12-02

To make the test case insensitive place argument within UPPER(...)

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"AA","")))/LEN("AA")

Find the number of cells in a range that have string as their value (#countif)

=countif(A1:J1,"aa") Assuming in row 1, columns A through J.

Find string between two different delimiters (period on left, @ on right)

Strip out McRitchie from David.McRitchie@myispNOSPAM.com

=MID(LEFT(A1,FIND("@",A1)-1),FIND(".",A1)+1,255)

Find String between two similiar delimiters (http:// and / to the right)

The previous method will not work if the second delimiter is also found earlier. The following will strip out www.abc.com from http://www.abc.com/... or https://www.abc.com/news/...

=LEFT(MID(B1,FIND("://",B1)+3,255),FIND("/",MID(B1,FIND("://",B1)+3,255))-1)

=IF(A1="","",LEFT(MID(A1,FIND("://",A1)+3,255),FIND("/",MID(A1,FIND("://",A1)+3,255))-1))

First Character checked as alphabetic character

Based on a posted reply by Denny Campbell 1999-02-23

The following returns TRUE if 1st character is a letter and FALSE if it doesn't.

=NOT(ISNUMBER(VALUE(LEFT(D7,1))))

Modified to reject blank cells meaning ISBLANK (empty, never used), and cells containing only space(s).

=IF(LEFT(TRIM(D7))="","",NOT(ISNUMBER(VALUE(LEFT(D7,1)))))

Frequency

Return the string that occurs most often: WS: http://groups.google.com/groups?oi=djq&ic=1&selm=an_313817206

Number of strings in a column: WS: =COUNTIF(D1:D50,f2)

Numbers to words (as in check protection wording) (#numberstowords)

Q213360 - XL2000: How to Convert a Numeric Value into English Words in Dollars and Cents

See KB Article Number Q140704 in Dollars and Cents

See KB Article Number Q95640 One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents

Dollars Function by Bernie Deitrick (works up to trillions) gives you a choice with or without “Dollars and Cents” wording.

=Dollars(111.11, TRUE) produces One Hundred Eleven Dollars And 11/100

=Dollars(111.11, FALSE) produces One Hundred Eleven and 11/100

To protect numbers format as: _($**#,##0.00_);_($**(#,##0.00);_($**"-"??_);_(@_)

Convert numbers to Rupees MS Excel Macro For Number To Word Conversion

as a macro can be found at http://pun1ww1-a.sancharnet.in:83/akl_valuer/down.htm converts any number upto 999999999.99 into words with Indian currency. Suggest converting to a User Defined Function.

=Rupees_as_text(999999999.99) produces

Rupees Ninety Nine Crore Ninety Nine Lakh Ninety Nine Thousand Nine Hundred & Ninety Nine - Paise Ninety Nine Only

=Rupees_as_text(589421) produces

Rupees Five Lakh Eighty Nine Thousand Four Hundred & Twenty One Only

Also see BHATTEXT available in Excel 2002 or you can simulate in earlier versions.

Convert numbers to Roman numerals See HELP --> ROMAN worksheet function

Position of First non alphabetic character (#firstnonalpha)

Function Pos_nonalpha(cell) As Integer 'posted misc 2001-07-23 David McRitchie Dim i As Integer For i = 1 To Len(cell) Dim Number Select Case Asc(Mid(cell, i, 1)) Case 0 To 64, 91 To 96, 123 To 191 Pos_nonalpha = i Exit Function End Select Next i Pos_nonalpha = 0 End Function

Most of 192 to 255 are characters of the Latin1 Font are in other languages with the exceptions of 215 and 247, so you can refine the above to suit your data. Also see synbols, unicode).

65-90 are uppercase letters, 97-122 are lowercase letters.

Problems with Data, Particularly data pasted from HTML

The following Worksheet Functions can be very useful in determining exactly what you have for your data as Excel sees it -- it may not be what you think it is. You may have problems with Text and Numbers in a column that you try to sort. For more information on finding out what the problem with the data is see join.htm#debugformat and for removing CHAR(160) the   or   (non-breaking space in HTML) and extra spaces using a macro see the TRIMALL macro, also read about formulas and formats on my Formulas page.

=ISNUMBER(E2)

=ISTEXT(E2)

=LEN(E2)

=ISBLANK(E2) [in VBA the equivalent is ISEMPTY]

=CODE(E2) =CODE(RIGHT(E2,1)) [in VBA the equivalent is ASC]

=personal.xls!GetFormula(E2)

=personal.xls!GetFormat(E2)

String Comparisons, compare to second space (based on posting 2003-01-090

Formulas include concatenation of two spaces to guarantee finding the second space in each case.

a14: BA 12345 10-30-2003 must be ---> BA 12345 B14: NM 12345 AB 40893 Must be ----> NM 12345 D14" =IF(UPPER(LEFT(A14 & " ",FIND(" ",RIGHT(A14 & " ",LEN(A14 & " ") -FIND(" ",A14 & " ")))+FIND(" ",A14 & " ")))= UPPER(LEFT(B14 & " ",FIND(" ",RIGHT(B14 & " ",LEN(B14 & " ") -FIND(" ",B14 & " ")))+FIND(" ",B14 & " "))),"Equal","Differs") or using a programmed User Defined Function A14: BA 12345 x B14: BA 1234 C14: ba 12345 D14: =CompareTruncate(A14,B14) -- shows Differs E14: =CompareTruncate(A14,C14) -- Equal Function CompareTruncate(ByVal string1 As String, ByVal _ String2 As String) string1 = Left(string1 & " ", InStr(InStr(string1 & " ", " ") + 1, _ string1 & " ", " ")) String2 = Left(String2 & " ", InStr(InStr(String2 & " ", " ") + 1, _ String2 & " ", " ")) '-- CompareTruncate = UCase(string1) = UCase(String2)--replaced by... CompareTruncate = InStr(1, String2, string1, vbTextCompare) = 1 End Function

Return string after last “/” character

Posted on or before 2000-02-19, though still missing (D.McRitchie)

=AFTLAST(E22,"/") Function AFTLAST(cell As Range, findchar As String) As String Application.Calculation = xlCalculationManual 'in XL97 For i = Len(cell) To 1 Step -1 If Mid(cell, i, 1) = findchar Then AFTLAST = Mid(cell, i + 1, 99) Exit Function End If Next i AFTLAST = cell ' or aftlast="" depending on what you want Application.Calculation = xlCalculationAutomatic 'in XL97 End Function

Return string after first “/” character

Remove everything to left of first “/”

=IF(ISERR(FIND("/",E22)),E22,MID(E22,FIND("/",E22,1)+1,99)) or =IF(ISERR(FIND("/",E22)),"",MID(E22,FIND("/",E22,1)+1,99)) depending on whether you want cell value or nothing when "/" is not present.

Rearranging Data in Spreadsheet Columns

Rearrange columns by splitting, joining, or reversing columns of data.

MarkCells() Creates test data by placing the cells address as the value for each cell in the selected range.

Join columns together

Join() can be used as a reversal of a correctly executed text to columns.

Separate terms across columns

SepTerm() can be used as a more limited version of text to columns that only separates from the first word.

Separate at second term. First word remains in place, second and remainder go into the next column. This is accomplished using SepTerm() with a two column range. The first term will remain and the remainder will go into the second column.

SepLastTerm() separates last word from from beginning of text.

Reverse the order of items in a row, column, or range

ReversI() is used to reverse the order of items in a row, column, or range.

Remove All Spaces (#removespaces)

=SUBSTITUTE(A1," ","")

Removing Unprintable Characters

It would be advisable to find out exactly what the characters are, because you might want to do different things with different “unprintable” characters. If so you might want to use REPLACE to replace characters with a space, instead of the CLEAN which will remove them. i.e. Youmaynotwantitlikethis.

Returns a numeric code for the first character in a text string

Worksheet Function: =CODE(A1)

VBA: MsgBox ASC(cell.value)

9 = TAB, 10=LF, 13=CR, 160 is non breaking space ( )

(line feed, carriage return, if both are used CRLF)

Perhaps more on my Symbols page in a different directory: http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm

Just to keep things in perspective:

To find the ASCII code of a single pasted character use CODE(char):

=CODE("A") yields 65 for "A", Alt+0065 is "A"

=CHAR(65) yields "A" for code 65

Also see the TRIMALL macro and detailed explanations concerning CODE(160) &nbsp (nonbreaking space character), substitutions and removal, along with differences between Excel TRIM and VBA TRIM.

Remove illegal characters from filename (#filenames)

Function ReplaceIllegalChars(Filename As String) As String 'leo.heuser@get2net.dk, 5. August 2001, programming Dim Illegal As Variant Dim Counter As Integer Illegal = Array("<", ">", "?", "[", "]", ":", "|", "*", "/") For Counter = LBound(Illegal) To UBound(Illegal) Do While InStr(Filename, Illegal(Counter)) Mid(Filename, InStr(Filename, Illegal(Counter)), 1) = "_" Loop Next Counter ReplaceIllegalChars = Filename End Function

StrReverse, Reverse Character String (#strreverse)

Return the string after the last “\”, or the entire string if the back slash is not present.

newString = Mid(yourString, InStrRev(yourString, "\", , vbTextCompare) + 1)

StrReverse was introduced in Excel 2000. You can write a UDF for use prior to Excel 2000, and if you want to use StrReverse in the worksheet you must use a UDF see strReverse User Defined Function.