Strings and Manipulations
Location:   http://www.mvps.org/dmcritchie/excel/strings.htm
[View without Frames]

Text Functions, Workbook
Before continuing please be aware of the following information available in Excel HELP

FIND(find_text,within_text,start_num)
WorksheetDescription VBA
CHARReturns the character specified by the code number CHR
CLEANRemoves all nonprintable characters from text
Characters 0-31, 129, 141, 143, 144, and 157
CLEAN
CODEReturns a numeric code for the first character in a text string
If Asc("x") = y  is faster than  If Chr$(y) = "x" ASC CONCATENATE Joins several text items into one text item & or + DOLLARConverts a number to text, using currency format (none) EXACTChecks to see if two text values are identical (case sensitive) = FINDFinds one text value within another (case sensitive), also see SEARCH InStr [Excel]: FIND(needle,haystack,start_num) [VBA]: longval = Instr([start, ]haystack,needle], compare]) -- compare = 0 for binary FIXEDFormats a number as text with a fixed number of decimals ??? INDIRECTReturns a reference indicated by a text value =INDIRECT(C1) returns the value of the cell pointed to in C1 application.Evaluate ISBLANKRefers to an Empty Cell, Function returns True or False ISEMPTY ISNUMBERRefers to a Cell, Function returns True or False. (Not a string function) ISNUMERIC LEFTReturns the leftmost characters from a text value LEFT LENReturns the number of characters in a text string LEN LOWERConverts text to lowercase LCase MIDReturns a specific number of characters from a text string starting at the position you specify In VBA the third parameter is optional; the MID Worksheet Function requires all three parameters. MID, InStr PROPERCapitalizes the first letter in each word of a text value PROPER REPLACEReplaces characters within text In VBA prior to Excel 2K use application.Substitute REPLACE Application.Substitute REPTRepeats text a given number of times REPT (reverse)Reverses a character string (User Defined Function available) strReverse RIGHTReturns the rightmost characters from a text value RIGHT SEARCHFinds one text value within another (not case sensitive), also see FIND InStr [Excel]: =OR( NOT(ISERR(Search("Excel",D4,1))), NOT(ISERR(Search("XL",D4,1))) ) [VBA]: longval = Instr([start, ]haystack,needle[, compare]) -- compare = 1 for text comparison SUBSTITUTESubstitutes new text for old text in a text string (case sensitive) Application.Substitute TReturns the text referred to by value. If value is or refers to text, T returns value. If value does not refer to text, T returns "" (empty text). --- ---CStr has a single operand, and returns a recognizable text representation of a numeric value. CStr TEXTFormats a number and converts it to text FORMAT TRIMRemoves spaces from text (for VBA usage see TrimALL macro) WS Function remove duplicate inner spaces. VBA and WS function remove leading and trailing spaces. TRIM,ltrim, rtrim UPPERConverts text to uppercase UCase VALUEConverts a text argument to a number (none) (VBA only) Extracts leading digits ignoring embedded spaces. VAL Listed by Tom Ogilvy from HELP (VBA HELP), but I can't find which help. Action Keywords Compare two strings. StrComp Convert strings. StrConv Convert to lowercase or uppercase. Format, LCase, UCase Create string of repeating character. Space, String Find length of a string. Len Format a string. Format Justify a string. LSet, RSet Manipulate strings. InStr, Left, LTrim, Mid, Right, RTrim, Trim Set string comparison rules. Option Compare Work with ASCII and ANSI values. Asc, Chr For more information on equivalent worksheet function and Visual Basic code. HELP (F1) --> Answer Wizard: visual worksheet Programming and Language Reference Using Microsoft Excel worksheet functions in Visual Basic List of Worksheet Functions available to Visual Basic. [from VBA HELP] [L] Functions: To use an Excel function in VBA prefix with Application.WorksheetFunction. -- answer = application.substitute(haystack, needle, replacement) c = Len(v) - Len(Application.WorksheetFunction.Substitute(v, ".", "")) c = Len(v) - Len(Replace(v, ".", "")) '-- replace function avail in VBA Not strings but to use some worksheet functions like MIN, MAX answer = Application.WorksheetFunction.Min(myRange) Methods: Activesheet.Columns("A").Replace(What, Replacement, LookAt, _ SearchOrder, MatchCase, MatchByte ) '--replace method Set rng = ActiveSheet.UsedRange.Find("ANN") '-- like FIND shortcut (Ctrl+F), not case sensitive [Example] ## Case sensitivity (#sensitivity) In general Worksheet Functions are not case sensitive, and in VBA programming everything that is strictly VBA is case sensitive. WS Functions: to make something case sensitive as in Data Validation, change the “Allow” dropdown to Custom, then, assuming the active cell is A1, enter =Exact(A1,UPPER(A1)) to ensure uppercase, or =EXACT(A1,LOWER(A1)), to ensure lowercase, and click the “error alert” tab and create an appropriate error message. As mentioned in the table at the top, FIND is case sensitive, and SEARCH is not. SUBSTITUTE(value,from,to) is entirely case sensitive. UPPER, LOWER, and PROPER are not case sensitive but will convert the letter case. VBA: usage is case sensitive. Application. -- invokes an Excel function so it will be case sensitive if the Excel function is case sensitive. To make VBA not case sensitive you can use UCASE or LCASE as in IF UCASE(myvar) = "ABC" THEN ... There are a few VB functions that can be made case insensitive with an operand (InStr, Sort, Replace, Find, Select) InStr(1, Range("b2"), "text")  as binary comparison (default is 0: vbBinaryCompare) InStr(1, Range("b2"), "Text", 1)  as textual comparison (1: vbTextCompare) Replace: Replace(expression, find, replace[, start[, count[, compare]]]) Target.Value = Replace(Target.Value, "<u>", "", , vbTextCompare) Another way in VBA would be to use "Option Compare Text" but this would probably be unexpected coding and confusing.  Original Result toString fromString abcdef AbCdEf ACE ace abcdefg aBcdefg aB Ab abcdefgh AbcDefgh DRATs drats Abcdefghijk Ayxwvughijk zyxwvu abcdef B2: =personal.xls!translate(A2,C2,D2) User Defined Function to simulate REXX and PL/I Translate: You would have to write your own function to simulate a very basic translate function (#translate) newString = translate(oldString, toString, fromString) ## Wildcard Characters (#wildcards ?, or *, or ~) The Search Worksheet Function is not case sensitive and allows wildcards, precede wildcard characters of ?, or *, or ~ with a ~ (tilde) to match actual character. The FIND Worksheet Function (case sensitive) does not allow wildcards. Wildcards: ? single character, * any number of characters. When using SEARCH to obtain True (found) or False (not found) it will be necessary to test for ISNUMBER to eliminate the return of "#VALUE!" if not found. =ISNUMBER(SEARCH("* anymatch *"," " &$A1 & " "))

Find (Ctrl+F) and Replace (Ctrl+H) shortcuts are not case sensitive and allow wildcards and overrides of wildcards.

Examples of ”wildcard” use can be found in SEARCH and FIND among some Conditional Formatting examples.

Don't confuse with ampersand(&) formatting text in headers and footers (to type a plain ampersand double them as in AT&&T.

## Check Validity of Data (#debugformat)

Worksheet Functions to determine the actual data content: ISNUMBER, ISTEXT, LEN, ISBLANK, CODE.  (see Debug Format on my Reformatting (join.htm) page.

## REPLACE   (#replace)

REPLACE Worksheet Formula

    SUBSTITUTE(text,  old_text,  new_text,  instance_num)

=SUBSTITUTE(A1,  ", ",   ",")


VBA     for a range, several examples   (in VBA prior to Excel 2K use application.Substitute)

   Activesheet.Columns("A").Replace(What, Replacement, LookAt, _
SearchOrder, MatchCase, MatchByte)   '--replace method

rng.Replace what:= ", ", replacement:= ",", _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False

Intersect(Selection, Selection.SpecialCells(xlConstants, _
xlTextValues)).replace(", ",",")


## Material introduced in VB6 with Excel 2000 not available on Macs   (#vb6)

The following string functions are not available in VB5 use before Excel 2000 and are not available on MACS.

FunctionDescription
JoinUsed to join arrays elements.
SplitSplit a string into a variant array.
InStrRev  Similar to InStr but searches from end of string.
ReplaceTo find a particular string and replace it.
ReverseTo reverse a string.

MSKB 188007 -- How To Simulate Visual Basic 6.0 String Functions in VB5

## Concatenation & Quotes (#quotes)

The following are equivalent

To indicate a quote you double the quote within text, so your orginal VBA example could be simplified to.

msgbox "hello and ""Goodbye"""

William's method can keep things straight if you have several layers of quoted material,
but I believe he meant to use
msgbox = "Hello and " & chr(34)& "Goodbye" & chr(34)

Your original would work if you doubled the enclosed quotes:
msgbox "hello and " & """ & "Goodbye" & """"

On the worksheet the equivalent would include any of the following:
="hello and ""Goodbye"""
="hello " & char(34) & "Goodbye" & char(34)
="hello " & """" & "Goodbye" & """"
=concatenate("hello ", char(34), "Goodbye", char(34))


## Converting Text Data (#convert)

Changing character case to proper, lowercase, or uppercase

programmed VBA functions:
PROPER(Proper), LOWER (LCase), and UPPER (UCase)
Note the VBA example for Proper contains extra code to not capitalize the first letter of certain words after the first word.

Worksheet Functions:
=PROPER(),  =LCASE(), and = UPPER()

## Extract the first two initials (#extract)

Knowing your data is important for this, basically assumes you can obtain two initials.  Though a space has been concatenated to cell A37 in the example to prevent a #VALUE! failure.

=UPPER(LEFT(A37,1)&"."&MID(A37,FIND(" ",A37&" ")+1,1)&". ")

## Extract the first word

Extract the first word, contains extra code so it will not display a zero when the source cell is empty, and won't fail if there is only one word in source.

=IF(LEN(A11)=0,"",IF(ISERR(FIND(" ",A11)),A11,LEFT(A11,FIND(" ",A11)-1)))

## Extract the first word after a specific word

Find the word after "P/N"
=MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1)

Extract the word after "P/N" with allowance for empty cell

=IF(LEN(A11)=0,"",IF(ISERR(formula),"",formula)

=IF(LEN(A11)=0,"",IF(ISERR(MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1)),"",MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1))

## Extract the portion after space/hyphen/space

Extract the second part defined as after a space-hyphen-space.

=IF(ISERROR(FIND(" - ",A1)),"",MID(A1,FIND(" - ",A1)+3,999))

If find_text does not appear in within_text, FIND and FINDB return the #VALUE! error value.

You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn't allow wildcard characters.

## Extract the portion between the Parenthesis or other delimiters

=IF(ISERROR(FIND(")",A1)),"",IF(ISERROR(FIND("(",A1)),"",LEFT(RIGHT(A1,LEN(A1)-FIND("(",A1)),FIND(")",RIGHT(A1,LEN(A1)-FIND("(",A1)))-1)))

Breaking apart the above to visualize:
Sample data:
Peachtree Ltd (Boston), L.C. Casey, Manager

The following returns “(Boston)” if both parens are present
=RIGHT(A1,LEN(A1)-FIND("(",A1))
The following uses above formula, removing both parens, and checks for ISERROR
=IF(ISERROR(FIND(")",A1)),"",IF(ISERROR(FIND("(",A1)),"",LEFT(F1,FIND(")",F1)-1)))

## Find number of characters in a Cell or in a Range   (#countchars)

WS:   =LEN(A1)
VBA:  = Len(Rng.Value)

SUBSTITUTE Worksheet Function is case sensitive to make an insensitive comparison both the from and the target must match in case, by using UPPER on the the target, and by using uppercase character within quotes.

Find number of occurrences of a character in a cell     i.e. A1: 'banana
keeping in mind that SUBSTITUTE itself is case sensitive
=len(a1)-len(substitute(a1,"a",""))     — lettercase must match
=len(a1)-len(substitute(upper(a1),"A",""))     — insensitive to lettercase, but the case of the from
in the argument must match that of the UPPER(target).

Number of Characters including spaces in a cell
=LEN(A1)
Non blank characters in a cell
=LEN(SUBSTITUTE(A2," ",""))
Blank characters in a cell
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Number of cells in a range containing at least one occurence of a character
=COUNTIF(range,"#") -- Alan Beban 2003-08-29
Number of occurences of a character or string in a range of cells counts muliple occurences within each cell --Tom Ogilvy 2003-08-29
=SUM(LEN(H16:H19)-LEN(SUBSTITUTE(H16:H19,"#",""))) -- array formula enter with Ctrl+Shift+Enter
=SUMPRODUCT(LEN(H16:H19)-LEN(SUBSTITUTE(H16:H19,"#",""))) -- normal entry
Number of CAPITAL letters in a cell
(Dave Peterson, worksheet solution posted in Programming, 2003-04-27)
=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),""))) -- array solution
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),""))) -- non array solution
Number of CAPITAL letters in a Range of cells
(Dave Peterson, worksheet solution posted in Programming, 2003-04-27, array solution)
=SUM(LEN(A1:A10) -LEN(SUBSTITUTE(A1:A10,TRANSPOSE(CHAR(ROW(INDIRECT("65:90")))),"")))
Number of CAPITAL letters in a cell or Range of cells (revisited)
Peo Sjoblom 2001-12-25, non array solution
=SUM(LEN($A$1:$A$50)-(LEN(SUBSTITUTE(UPPER($A$1:$A$50),UPPER(B1),""))))
use lower in both places to find number of lower case letters.
Number a specific CAPITAL letters in found in the leftmost postion in a Range of cells
by Peo Sjoblom 2007-09-22
=SUMPRODUCT(--(EXACT(LEFT(B4:B7),"Y")))

Additional example appear elsewhere some of the other examples are actually the same as those within this topic, basically you can use a char or a string of characters interchangeably.

## Find the number of items separated by commas (#countitems)

Will return 2 if but a single comma, but the main thing was to show zero if the cell is empty or contains only spaces
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+(LEN(TRIM(A1))>0)

## Find the nth word in a string (#nthWord)

ExtractElement returns “0133”, which is the fourth element in the string. The string uses a hyphen (-) as the separator in the following example:

=ExtractElement("123-456-789-0133-8844",4,"-")

Code for ExtractElement at http://www.j-walk.com/ss/excel/tips/tip32.htm

An alternative is Laurent Longre's WMID (part of his free MOREFUNC.XLL add-in, available at http://longre.free.fr/english)

=TRIM(WMID(A1,1,1,"~"))

A website that will help you translate Laurent's French website to English while browsing and using hyperlinks.
Babelfish  [http://babelfish.altavista.com/]

A strictly worksheet function alternative supplied by KL an Excel MVP in Russian Excel newsgroups:

A1: one-two-three-four-five-six-seven-eight     [hyphen ("-") for delimiter, and find 4th word]

=INDEX(MID(A1,FIND("|",SUBSTITUTE("-"&A1,"-","|", ROW(INDEX(A:A,1):INDEX(A:A, 1+LEN(A1) -LEN(SUBSTITUTE(A1,"-","")))))), FIND("|", SUBSTITUTE(A1&"-","-","|", ROW(INDEX(A:A,1):INDEX(A:A, 1+LEN(A1) -LEN(SUBSTITUTE(A1,"-",""))))))-FIND("|", SUBSTITUTE("-"&A1,"-","|",ROW(INDEX(A:A,1): INDEX(A:A, 1+LEN(A1) -LEN(SUBSTITUTE(A1,"-",""))))))),4)
and he also pointed out it could be made shorter by substituting
=ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))
could be shortened to
=ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))
or even made smaller with an array formula under certain conditions.

## Find the last word in a string (#lastword)

Don't ask me how this works it was posted 1998/05/29 by Laurent Longre.  This will find the last word in cell A1.  Works at least in XL95 and XL97.

=TRIM(CALL("Crtdll","strrchr","CCI",A1,32))

The above does not work in Excel 2000 because CALL was disabled in Excel 2000 and some of the later bug fixes in Excel 97.  In reality if the purpose to to separate names I would have been using a macro such as SepLastTerm() separates last word from beginning of text with the last term being moved to the cell on the right.  The next example provides a means of finding the last word use a space (“ ”) instead of backslash (“\”).

## Find the last word in a string (revisited)

Finding the content after the last occurrence of a delimiter.  This solution presented by Ken Wright, 2003-06-26, will work in all versions of Excel.  Note the use of the 3rd parameter of Substitute.  Using a space (" ") instead of backslash ("\") provides the last word in a string.

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

 A B C 1 a\b\c\dd\ee\ffff ffff =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))) 2 abcddeeffff =SUBSTITUTE(A1,"\","") 3 16 =LEN(A1) 4 11 =LEN(SUBSTITUTE(A1,"\","")) 5 a\b\c\dd\ee*ffff =SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) 6 a\b\c\dd\ee*ffff =SUBSTITUTE(A1,"\","*",5)

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 (#first-n-words)

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))

### Separate Name and Email from combined entry (#email)

A1:  BernardRussell(russales@example.com)
B1:  =IF(A1="","",LEFT(A1,FIND("(",A1)-1))  -- user name
C1:  =IF(A1="","",MID(A1,FIND("(",A1)+1, LEN(A1)-LEN(B1)-2))  -- email
D1:  =IF(A1="","",SUBSTITUTE(MID(A1,FIND("(",A1)+1,1024),")","")) -- email w/o B1


## Count Blanks or Padding on Left

The following two User Defined Functions have been extracted from my BuildTOC page.  It may be of interest to indicate the padding count (level) for a cell, particularly after pasting bookmarks into a worksheet.

If you paste bookmarklets.html or favorites into Excel you can extract the hyperlinks, and you can find how much padding is in the cell according to the IndentLevel which can be determined with the following function.

Private Function indented(cell) As Long
'Leftside Padding level (count),   D.McRitchie 2007-07-26
indented = cell.IndentLevel   'cell padding on left
End Function

Private Function LSpaces(text) As Long
LSpaces = Len(text) - Len(LTrim(Replace(text, Chr(160), " ", , , vbTextCompare)))
If LSpaces = Len(text) Then LSpaces = 0
End Function


## 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)))))

Note the use of VALUE is needed because functions like LEFT and RIGHT always return Text.

## Formatting non-numbers with Hyphens

The following macro will format based on the text value of cell
so it should work on cells that have text, numbers, formatted
numbers with leading zeros.  It will make no difference that
the first character is a letter.    If it say commas or decimal
points they would just be characters.   the only thing this does
is insert hyphens based on your pattern.

Sub Format_with_hyphens(Optional formatSTR As String)
' D.McRitchie, 2005-04-03, misc  Format with Hyphens
'-- The parameter here can only be used if invoked
'-- from another macro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, j As Long, result As String
Dim cell As Range, newStr As String, tstLen As Long
On Error Resume Next   'In case no cells in selection
If formatSTR = "" Then
newStr = "Hxxx-xxx-xx-xxx-x"
Else
newStr = formatSTR
End If
tstLen = Len(Replace(newStr, "-", ""))
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Len(cell.Text) = tstLen Then
j = 1
result = ""
For i = 1 To Len(newStr)
If Mid(newStr, i, 1) = "-" Then
result = result & "-"
Else
result = result & Mid(cell.Text, j, 1)
j = j + 1
End If
Next i
cell.Value = "'" & result
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Normal cell formatting is covered on my formula.htm page — GetFormat showing normal cell formatting, and GetFormula (#cellformat)

## 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)

## HEX equivalent (#hex)

Converting a string to hexadecimal notation, and converting hexadecimal notation back to a string.

 A B C 1 original data hex string value of hex string 2 surf's up 737572662773207570 surf's up 3 J*M 4A2A4D J*M 4 ABC 414243 ABC 5 ABD 414244 ABD 6 A2: surf's up B2: =personal.xls!hexit(A2) C2: =personal.xls!dehex(B2)
Function hexit(str As String) As String
Dim I As Long  'renamed from Ron Rosenfeld's WEPConv, excel, 2005-04-26
For I = 1 To Len(str)
hexit = hexit & Hex(Asc(Mid(str, I, 1)))
Next I
End Function

Function dehex(str As String) As String
Dim I As Long  'David McRitchie, strings.htm,  2005-04-26
For I = 1 To Len(str) Step 2
dehex = dehex & Chr( _
InStr(1, "0123456789ABCDEF", Mid(str, I, 1)) * 16 - 16 _
+ InStr(1, "0123456789ABCDEF", Mid(str, I + 1, 1)) - 1)
Next I
End Function


## ISTEXT Worksheet Function & (#istext)

COUNTA, counts all non empty cells.  COUNT, counts numbers.  So the difference is the number of TEXT cells.
=COUNTA(A2:A7349)-COUNT(A2:A7349)

The following is an interesting method, it will count the number of cells in a range that are text as indicated by ISTEXT(A1)
=SUMPRODUCT(--(ISTEXT(C28:M28)))*0.5   – (Peo Sjoblom, 2005-05-05, misc )

For the equivalent in VBA see Macro to simulate StatusBar Reporting
msgbox Application.CountA(Selection.SpecialCells(xlCellTypeVisible))

## Create a Helper column to sort movie titles (#movietitles)

You are limited to a nesting level of 7 so if you want more than the following you would need a macro.  SUBSTITUTE is case sensitive so you have to put everything into the same lettercase and LOWER takes less space in your helper column.  You must place a space in front of the text so you can eliminate the first word.  Create a helper column

=trim(substitute(substitute(substitute(substitute(substitute( lower(" " & A2), " and "," ")," the "," ")," a "," ")," on "," ")," in "," "))

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

213360 - XL2000: How to Convert a Numeric Value into English Words in Dollars and Cents i.e. Thirty Two Dollars and Fifty Cents
See KB Article Number 140704   in Dollars and Cents (missing/deleted)
See KB Article Number 95640   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://www.charteredvaluer.com 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 another variation, this one uses the word POINT and eliminates trailing decimal fractional zeros. Display Excel numbers as words, Chris Mead. where 20001.0017 would be converted for display to: Twenty thousand and One point Zero Zero One Seven Numbers to Text is other European Languages -- Laurent Longre Download Laurent Longre's free morefunc.xll add-in from http://xcell05.free.fr/ found on his English page http://xcell05.free.fr/english/ and use his NBTEXT function, which is capable of many languages. (Ron Rosenfeld, 2005-05-26, worksheet.functions) NBTEXT supported languages: English, French (France-Québec), French (Belgium), French (Swiss), German, Dutch, Italian, Spanish, Portuguese, and Esperanto. NBTEXT documentation: 42 add-in worksheet functions (translated from French into English). VERSION 3.241 (2005-03-30) Position of First non alphabetic character (#firstnonalpha) Function Pos_nonalpha(cell) As Long 'posted misc 2001-07-23 David McRitchie Dim i As Long 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 &#160; or &nbsp; (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], also see Removing Unprintable Characters =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 (#removeunprintable) 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 Chip Pearson's “Cell View” addin makes viewing character codes within a cell easier than repeated use of CODE(cell). 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. ## Simulate Column Lettering Scheme (#col_letters) Simulate Column Lettering scheme but going down a row. (Greg Wilson, programming, 2006-03-29) From "A" to "ZZ" into Row 1 through Row 702. (702 = 26 + 26^2) =IF(INT((ROW()-1)/26)>0,CHAR(INT((ROW()-1)/26)+64),"") & CHAR(MOD(ROW()-1,26)+65)  Same formula but starting within Row 4 =IF(INT((ROW()-4)/26)>0,CHAR(INT((ROW()-4)/26)+64),"") & CHAR(MOD(ROW()-4,26)+65) Formula for "AA" to "ZZ" into row 1 through 676 -- Tom Ogilvy =CHAR(INT((ROW()-1)/26)+65)&CHAR(MOD(ROW(),26)+64+((MOD(ROW(),26)=0)*26)) Or to do same from row 4 through 679 using an address as the offset =CHAR(INT((ROW(A1)-1)/26)+65)&CHAR(MOD(ROW(A1),26)+64+((MOD(ROW(A1),26)=0)*26)) ## Variable type of a Dim x as variant (#variant) “Option Explicit” should be used on all modules even if you have to assign or default the dimension of a variable as variant. You can use typename later to find out how it was actually used. ## 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 Long 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. The following will probably be removed when topic is completed. Background for this topic Subject: VBA String Manipulation Utilities From: "Joe Latone" <latone@sphinx.com> Date: Mon, 5 Oct 1998 09:02:45 -0700 Newsgroups: microsoft.public.excel.programming Is there a repository of VBA string manipulation functions around somewhere? E.g., find the nth word in a string, find the number of words in a string, find the nth occurance of a word in a string, etc. I'll write my own if not, but these are such common, handy routines, I figured there might be a standard library that everyone uses. Thanks, Joe Subject: Re: VBA String Manipulation Utilities From: “John Walkenbach” <john@j-walk.com> Date: Mon, 05 Oct 1998 17:04:46 GMT I don't know of any repository, but the ExtractElement function at my web may be helpful: http://www.j-walk.com/ss/excel/tips/tip32.htm Subject: Re: VBA String Manipulation Utilities From: MyrnaLarson_nospam@csi.com (Myrna Larson) Date: Mon, 05 Oct 1998 22:23:30 GMT The book “VBA Developer's Handbook”, by Getz and Gilbert, ISBN 0-7821-1951-4,$50, has routines for these kinds of things.

A compact list of Worksheet Functions (overview) can be found as
Origin of Excel Worksheet Function Names -- http://www2.odn.ne.jp/excel/functioninenglish.html« by Janma,
the rest of his Excel site, “Excel Skill Training Hall”, is in Japanese.
Excel Function Dictionary « by Peter Noneley [alt link]
is a reference to definitely download and maintain on your computer, the workbook has 157+ sheets each with an explanation and example of an Excel function. /\
Newsgroups and Searching Newsgroups, Excel (xlnews.htm)
Some information on searching for information using Google Usenet Advanced Search Excel Newsgroups, and some information on searching the Microsoft Knowledge Database (KB).
ExtractDigits, User Defined Function,
extract first set of consecutive digits from a string.  Treat as a number if found as an empty string if none found (i.e., g25abc16 would return 25).  In the next posting in the same thread George Simms extracts set of digits from the leftmost postition only using an array formula (ctrl+shift+enter), but it fails for anything else (i.e. g25abc16 would fail).
=MID(A1,1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDIRECT("1:25")),1)),0)-1)*1
Followed by a posting after by Peo Sjoblom an array formula extracts a set of digits as a string from left/middle/right, but it fails if you have two sets of digits or no digits in the same string (i.e. g25abc16 would fail).
=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0), 100-SUM(1*ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))*1
Extraction of a Group of Digits and Dashes, posted by Harlan Grove,
use of Regular Expressions (RegExpr) in LIKE within VBA.
Proper, and other Text changes (proper.htm)
PROPER, LOWER (LCase), and UPPER (UCase) functions.  TrimSUB subroutine to TRIM cells in a selection.
Rearranging Data in Columns (join.htm).
Rearrange columns by splitting, joining, or reversing columns of data.  Join() can be used as a reversal of a correctly executed text to columns.  Lastname() can be used as a put lastname first is not already done.  SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from from beginning of text.  ReversI() is used to reverse the order of items in a row, column, or range.  RotateCW() is used to rotate a range of cells 90 degrees preserving formulas and formats.  Selection area must include cell A1.  MarkCells() is used to create test data within the invoked range.  MarkSepAreas() includes cell address and area number for creating test data across multiple ranges.  i.e. A1-1, B1-1, B2-2,C2-2,D2-2.  Additional material includes use of fill handle and creating a sheet with short cut keys for reference.
WordCount (wordcnt.htm)
Code for WordCount() was derived from ExtractElement coding.