Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Find a character in string Results

EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo

My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.

Greetings fellow Excel workers!

I have been searching for a solution but can't seem to find one regarding an issue that I have with a report that is being exported into Excel.

The report will export info into the first three columns in Excel, customer ID/name, and then two columns of numbers/percentages, something like:

Cell A1 Cell B1 Cell C1
ABC - ABC Co., LLC $xxxxx.xx 45%
Anderson - Anderson Technology, Inc. $xxxxx.xx 55%

I would like to edit the first columnn and remove the client ID portion only leaving the client name portion in the cell, or like this:

ABC Co., LLC $xxxx.xx 45%
Anderson Technology, Inc. $xxxx.xx 55%

This cell will always contain the character "-" which shold allow for the constant to perform the edit. I do use, and keep, the "-" in other portions of the report, so the macro can't look at the entire worksheet. I'm looking for a macro to allow me to enter in the range, or number of rows in column 1 to make this edit; the number of rows will vary from month to month report to report.

Any ideas anyone? Thanks in advance, as always, for your help!

Mark

Hi All,

I wanted to extract the 10112-1-2 from this code TKL-G11043-1105-BOC-10112-1-2. So i created this formula, =MID(B4,FIND("BOC",B4)+4,9) to do that.

But the problem is, some of the codes i have been given has longer characters than others, for example 10113-101-200 therefore the formula will only pick up 9 characters.

So my questions is.. how can i tell excel to pick the rest doesn't matter how long the character is?

Thanks very much in advance

Hi All,

Hopefully someone can assist me.

I have 2 worksheets. "Sheet1" and "Sheet2".

Sheet1 has a column containing a string of user entered comment text.

I can successfully use a macro to copy this column from Sheet1 to any given column in Sheet2.

The issue I have is that I have 3 columns, which represent 3 separate lines on a form in Sheet2. These columns each have a character limit of 29.

What I want to do is find a way of copying the text string from the column in Sheet1 and pasting the first lot of 29 characters into a column in Sheet2 (Column B for example), the second lot of 29 characters into the next column (Column C for example) and the 3rd lot of 29 characters into the next column (Column D for example).

Is this possible?

Thanks in advance.

Hi,

We have a number of cells in a column similar to these:

ABC123
ABC12E
ABC2A3

I am trying to find out the letter that is furthest to the right in these strings.

Example:

1. would be C
2. would be E
3. would be A

I know the RIGHT function will pull any character is on the right, but is there one for just letters and not numbers?

Thanks

I found this thread on the now defunct Google Answers that is spot on to my problem, but their solution didn't work:
http://answers.google.com/answers/threadview?id=319322

As you are probably aware, if you try to simply cut & paste a word table to excel, any carriage returns within a table cell will cause multiple rows in excel, and will cause the rest of the cells in that row that have no carriage returns to appear as merged cells.

In my case, I had a bunch of cells w/ soft carriage returns, but I was able to replace them using "^l" in the Find field in Word.

As described in the above thread, Excel does not recognize such codes as "^p" or "^l" in the Find/Replace fields, and anyway, I cannot simply use the Find/Replace function because I get a "Formula is too long." error.

Here is the code provided by aht-gt in the Google Answers thread:

Public Function ReplaceCharacter(ByVal s As String, ByVal oldchar As String, ByVal newchar As String) As String
Dim sVal As String

'Set the late binding objects
Dim rPart As Object

sVal = s

Set rPart = CreateObject("VBScript.RegExp")
rPart.Global = True
rPart.IgnoreCase = True
rPart.Pattern = oldchar

'replace all occurences of the pattern c with the
sVal = rPart.Replace(sVal, newchar)

Set rPart = Nothing

ReplaceCharacter = sVal

End Function

Sub MyReplace()
Dim s As String

On Error GoTo MyReplaceAbort

Application.DisplayStatusBar = True
Application.StatusBar = "Replacing Characters..."
Application.ScreenUpdating = False

For Each c In Selection.Cells
c.Value = ReplaceCharacter(c.Value, "~", Chr(10))
Next c

MyReplaceAbort:

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = True

End Sub

I used "qqq" as my dummy string, so I changed the "~" in his code to "qqq".
I ran the macro, and instead of replacing my dummy string w/ a line break, it replaced it with the string "^l". I also tried changing the "Chr(10)" in his code with "Chr(13)" and "Chr(10) & Chr(13)", and the results were the same.
How &^%$#@! asinine is that?
I have thousands of table rows, and it just isn't realistic fixing all of these carriage returns manually.

Can someone please suggest some alternatives here, or possibly explain why his code isn't working?

I'm running Excel 2003 SP 2.

Thanks.

In debugging a program, I isolated the problem to the following issue.
I manually created the following situation in column A, rows 1 to 6, by
copying and pasting actual data in order to accurately re-create the
problem:

ABN/ACN/BN
*

ABN/ACN/BN
3158816
40000545415/005

Excel "Help" has this to say about the asterisk:
" * (asterisk) Any number of characters in the same position as the
asterisk
For example, *east finds "Northeast" and "Southeast" "

I am assuming that the asterisk, being the wild card symbol, being the
"cell not empty" symbol, will result in the display of both the strings
under the title ABN/ACN/BN (that is, "3158816" and "40000545415/005").
But no! The string "3158816" mysteriously disappears, leaving just
"40000545415/005". The "List Range" and "Criteria Range" appear
correct.

Would anyone mind explaining to me what horrible assumption I have made
now, why on earth this is happening, and what I need to do to correct
it? And, if anyone has the time, would someone mind explaining to me
how I could have worked this out for myself and not bothered you good
people? I really am trying to be self-sufficient here. Thanking you in
anticipation.

1. The functions SEARCH(), SEARCHB(), FIND() and FINDB() return a #value
error if they didn't find the "find_text" string within the "within_text"
string. This forces the user to use long formulas such as
=IF(ISERROR(SEARCH(A1, A2)), 0, SEARCH(A1, A2)) in order to avoid errors.
Since in case of success, these functions return the serial number of the
first matching character, starting from 1, I suggest that these functions
return either 0 or -1 if they can't find that text.
2. I suggest to add functions that count the number of times that one text
exists within another, for example:
FINDNUM(find_text, within_text), SEARCHNUM(find_text, within_text)
FINDNUM is case-sensitive, SEARCHNUM is not.
Find_text is the text you want to find.
Within_text is the text containing the text you want to find.
Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi!

The seach/3 function searches for 'find_text' in
'within_text', starting at the 'start_nun's character:

SEARCH(find_text,within_text,start_num)

I need to find the first digit in a string. I guess,
search/3 does neither support regular expresseions,
no does it allow a function instead of a constant
'search_text'.

One way to find the first digit is this monster:

=MIN(
IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
)

Any more efficient way to do a

search('[0-9]';D2) or search(isnumber();D2)

?

In the attached sample I have tried to find a way to change the background color of a cell depending on the content. But empty cells seem to frustrate my text string conditions. I'm also perplexed by the way Excel 2007 adds it's own quotes to text string entries. I have to constantly re-edit a condition to remove extra quotes.

I'm using the 'Format only cells that contain' rule to apply the CF across a range that defines a given row. That rule lets me place a formula in the cell reference box - but they do not appear to function as they did in 2003.
I also tried the 'Use a formula to determine which cells to format' rule with no better result.

Row 2 has no ConFor, it is supplied as an example of what I'd like to see.
Row 4 shows how Cell Value <> "A" conditionally formats empty cells. The rest of the rows are my efforts to ignore the empty cells.

Row 6 almost works, but row 7 shows how a different 'not between' value set fails.
I want to ignore a designated character, and empty cells.

Am I up against my ignorance - or a bug?
Tried a less detailed post here http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If I missed an answer to this I am sorry. My searches did not find what I wanted to know.

New to Excel 2007 and already extremely irritated.

I would like to find stuff within a formula and replace it with other stuff. I frequently did this in 2003 without issue. This is what I'm trying to do.

Problem #1
Select a range of cells with formulas
Example formula: =(-2/D4-E4)*F4-2*'Individual Scores'!B2
Find and Replace 2* with 2*$G$4

I receive the error message:

Your formula is missing a parenthesis . .. . . . . .

Problem#2
Select a range of cells with formulas
Example formula: =(-2/D4-E4)*F4-2*'Individual Scores'!B2
Find and Replace 2* with nothing (litereally nothing, not a string)

I receive the error message:

Your formula is incomplete. You must include and operand . . .

Is this due to the wildcard character searches (*,?) and me using a simple * instead of using Product(2,'Individual Scores'!B2) to multiply two values? If so is there a way to turn off wildcard character searching?
i.e. how and the heck would I find 2* and replace with product(2,'Individual Scores'!B2)

freakin' upgrades . . . .

I am using this formula
to extract an asset number from text string in cell H3 such as:

blah,blah,blah,blah(Asset No 123456)

This returns the result 123456) the -1 at the end of the formula is supposed to reduce the character length by one to remove the closing bracket but it doesn't interestingly I can increase this number and it makes no difference

What am I missing here? or maybe you can suggest a better way of extracting the asset number

BTW the asset number is not always a fixed length

I'm trying to find whether three characters occur in a string of text. I've tried a formula such as =IF(OR((FIND("*",A1,1))>0,(FIND("$",A1,1)>0),(FIND("*",A1,1)>0)),1,0) but I get a #value error. I've attached an example worksheet. What am I doing wrong with the formula?

I am trying to find a formula that will look at this 12 character string:

BOX00A8Y0L39

And return the 10th and 11th characters.

Thank you in advance.

Hello all

I need to have a cell making multiple substitutes to a text string. To be
specific, I have a text string in a cell and I want to have Excel look at
this string and wherever it finds a Greek character, convert it to a Latin
character (ie. Greek A to Latin A, Greek B to Latin B etc.). With the
substitute function, I can have only one character per function.

Is this possible by any other function i do not know?

Thanks

I have a huge mailing list and I have a macro to find and replace certain critera, the goal is to seperate these addresses into 2 parts.
the street address in ADDRESS1 and the apt/suites in ADDRESS2, If it doesnt have a apt/suite # then it should be blank.

What I want to do is find "* st" and as long as "st" is the end of the string then replace with ""

for example:

A1) 526 West 26th Street, Suite 920
A2) 526 W. 26th St. Ste 920
A3) 253 W. 23th St
A4) 235 34th st Apt 3
A5) 343 lafayette st

so that after I run the macro I have in my ADDRESS2 the following:

B1)Suite 920
B2)Ste 920
B3)
B4)Apt 3
B5)

If I do a simple Find and replace of "*st" I come up with the problem of replacing parts of A2 and
im left with "e 920" which is not what I want.
I want to be able to create multiple search criterias and not just those that have " st" at the end of the
string.
Does anyone know when using a Find and Replace if there is a special character to indicate that its the end of the string?

A3) 253 W. 23th St
that way i can Find " st(Special Character to indicate end of string)" replace with ""

thanks.

I have a macro that I want to edit. Right now the script works if all files are in the same folder. I need it to search in a directory for subfolders in a certain name range. The directory will not change. The subfolders names change by the last 2 characters. ex: TestCell_20**. I need the script to search just folders of this name type. Then I need the script to collect the data and input the data into the same workbook as its own sheet. Each subfolder contains a *.dat file from which excel will extract data. The code I have is below.
Sub open_files()

Dim name As String
' takes file name that starts with CellDaily_Stand20*.dat and copies it into "name" variable
name = Dir("C:Documents and SettingsdhughMy DocumentsTestCellsCellDaily_Stand20*.dat")

'loops copying each file as a sheet into Test_Data_Summary workbook

Do

Workbooks.Open Filename:="C:Documents and SettingsdhughMy DocumentsTestCells" & name
Workbooks(name).Sheets(1).Copy
After:=Workbooks("Test_Data_Summary.xlsm").Sheets(Workbooks("Test_Data_Summary.xlsm").Sheets.Count)
Workbooks(name).Close

name = Dir

Loop Until name = ""


End Sub

Sub gather_info()

Dim i As Integer
Dim s As Integer
Dim n As Integer
Dim y As Integer
Dim m As Integer
Dim tot As Double
Dim name As String

i = Sheets.Count

While name <> "Data Summary" 'loops through all sheets

s = 1 ' s is the row variable
While Sheets(i).Cells(s, 2).Value = 0 'finds first row with a date on the sheet
s = s + 1
Wend

While Sheets(i).Cells(s, 6).Value <> 0 'loops until last date on sheet

'copies month and year of one cell into variables
y = year(Sheets(i).Cells(s, 6).Value)
m = month(Sheets(i).Cells(s, 6).Value)

n = 0 ' n indicates which year the value should be copied under
While Sheets("Data Summary").Cells(6, (4 + 12 * n)).Value <> y 'loops to find correct year in data summary

If Sheets("Data Summary").Cells(6, (4 + 12 * n)).Value = 0 Then 'writes year into data summary
Sheets("Data Summary").Cells(6, (4 + 12 * n)).Value = y
Else:

n = n + 1

If Sheets("Data Summary").Cells(7, (3 + 12 * n)) = 0 Then ' makes new year column in data summary if needed

Sheets("Data Summary").Range("C7:N7").Copy Destination:=Sheets("Data Summary").Cells(7, (3 + 12
* n))
Sheets("Data Summary").Range("C6").Copy Destination:=Sheets("Data Summary").Cells(6, (3 + 12 *
n))

End If
End If


Wend

tot = 0 'the monthly total for a test cell
While month(Sheets(i).Cells(s, 6).Value) = m And Sheets(i).Cells(s, 6).Value <> "" 'loops while still the
same month

'tests logic to check if the difference between test cell hour values makes sense
If Sheets(i).Cells(s + 1, 17).Value - Sheets(i).Cells(s, 17).Value > 0 And Sheets(i).Cells(s + 1, 17).Value -
Sheets(i).Cells(s, 17).Value < 10 Then
tot = tot + Sheets(i).Cells(s + 1, 17).Value - Sheets(i).Cells(s, 17).Value
'adds difference to total for month
End If

s = s + 1

Wend

'copies total into data summary
Sheets("Data Summary").Cells((37 + i - Sheets.Count), (2 + m + 12 * n)).Value = tot

Wend

i = i - 1
name = Sheets(i).name

Wend


End Sub


I am trying to develop a UDF that will calculate the mass of a formula. In order to do that I need to be able to extract the element and it's associated number (e.g. C and 20). If the string C20H22 is in cell A1 and Emass(A1) is entered into A2 the result of 240 is correctly shown in cell A2 (mass of C = 12, 20 * 12 = 240) when the top level Do While Loop is made inactive by making it a comment (as shown below). However if the top level While-Loop is uncommented the function fails. The top level loop is supposed to go through the entire formula and revise the mass as a new element and number is found. Any ideas why the top level Do While Loop is causing the UDF to fail?

Function EMass(ChemFormula As String) As Double

On Error GoTo FuncDied:

Dim Elem As String
Dim ElemNumber As Integer
Dim ElemMass As Double
Dim TempMass As Double
Dim i As Integer
Dim n As Integer

i = 1
n = 1

'Do While i <= Len(ChemFormula)

    Do While IsCap(Mid(ChemFormula, n, 1))

    Elem = Elem & Mid(ChemFormula, n, 1)

    n = n + 1

    Loop
    
ElemMass = Application.VLookup(Elem, ThisWorkbook.Worksheets("DataBase").Range("table"), 2, 0) ' Find
element and get mass
    
    Do While IsNum(Mid(ChemFormula, n, 1))

    ElemNumber = ElemNumber & Mid(ChemFormula, n, 1)

    n = n + 1

    Loop

TempMass = ElemNumber * ElemMass + TempMass

i = n + 1

'Loop

EMass = TempMass

Exit Function

FuncDied:
  mySum4 = CVErr(xlErrValue)

End Function

Private Function IsNum(letter As String) As Boolean ' Determines if a character is a number
    '48 (0) to 57 (9)
    If Asc(letter) > 47 And Asc(letter) < 58 Then
        IsNum = True
    Else
        IsNum = False
    End If
End Function

Private Function IsCap(letter As String) As Boolean ' Determines if a character is a uppcase letter
    '65 (A) to 90 (Z)
    If Asc(letter) > 64 And Asc(letter) < 91 Then
        IsCap = True
    Else
        IsCap = False
    End If
End Function


was wondering if anyone can please help me with this task I would to create a userform that will search a workbook in excel with mulitiple sheet, it could be 5-endless worksheets The way i would like to do this is a new tab is created when a new client is add to our database and be able to seach curtain critiera such Hospital name, Contact name,services,phone numbers(if possible)and User name (if Possible) It sounds more complacted then it seems i am going to try to attach the file and code is there anyone whom can help i will be thankful

[Option Explicit]

'Module Level Variables
Dim rRange As Range
Dim strFind1 As String
Dim strFind2 As String
Dim strFind3 As String

Private Sub ComboBox1_Change()
'Pass chosen value to String variable strFind1
strFind1 = ComboBox1
'Enable ComboBox2 only if value is chosen
ComboBox2.Enabled = Not strFind1 = vbNullString
End Sub
Private Sub ComboBox2_Change()
'Pass chosen value to String variable strFind1
strFind2 = ComboBox2
'Enable ComboBox3 only if value is chosen
End Sub

Private Sub CommandButton1_Click()
'Procedure level variables
Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range
Dim rCell2 As Range
Dim rCell3 As Range
Dim bFound As Boolean

'At least one value, from ComboBox1 must be chosen
If strFind1 & strFind2 & strFind3 = vbNullString Then
MsgBox "No items to find chosen", vbCritical
Exit Sub 'Go no further
ElseIf strFind1 = vbNullString Then
MsgBox "A value from " & Label1.Caption _
& " must be chosen", vbCritical
Exit Sub 'Go no further
End If

'Clear any old entries
On Error Resume Next
ListBox1.Clear
On Error GoTo 0

'If String variable are empty pass the wildcard character
If strFind2 = vbNullString Then strFind2 = "*"
If strFind3 = vbNullString Then strFind3 = "*"

'Set range variable to first cell in table.
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strFind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)

'Loop only as many times as strFind1 occurs
For lCount = 1 To lOccur
'Set the range variable to the found cell. This is then also _
used to start the next Find from (After:=rCell)
Set rCell = rRange.Columns(1).Find(What:=strFind1, After:=rCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'Check each find to see if strFind2 and strFind3 occur _
on the same row.
If rCell(1, 2) Like strFind2 And rCell(1, 3) Like strFind3 Then
bFound = True 'Used to not show message box for no value found.
'Add the address of the found cell and the cell on the _
same row but 2 columns to the right.
ListBox1.AddItem rCell.Address & ":" & rCell(1, 3).Address
End If
Next lCount

If bFound = False Then 'No match
MsgBox "Sorry, no matches", vbOKOnly
End If
End Sub

Private Sub CommandButton2_Click()
'Close UserForm
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Application.Goto Range(ListBox1.Text), True
End Sub

Private Sub UserForm_Initialize()
'Procedure level module
Dim lRows As Long

'Set Module level range variable to CurrentRegion _
of the Selection
Set rRange = Selection.CurrentRegion
If rRange.Rows.Count < 2 Then ' Only 1 row
MsgBox "Please select any cell in your table first", vbCritical
Unload Me 'Close Userform
Exit Sub
Else

With rRange
'Set Label Captions to the Table headings
Label1.Caption = .Cells(1, 1)
Label2.Caption = .Cells(1, 2)
Label3.Caption = .Cells(1, 3)

'Set RowSource of ComboBoxes to the appropriate columns _
inside the table
strSheet = ListBox1.List(ListBox1.ListIndex, 1)
strAddress = ListBox1.List(ListBox1.ListIndex, 2)

End With
End If
End Sub

Private Sub UserForm_Terminate()
'Destroy Module level variables
Set rRange = Nothing
strFind1 = vbNullString
strFind2 = vbNullString
End Sub
Attached Files

I have a spreadsheet which populates some numbers from another software

One column has CUSIP numbers of securities and all these are 9 characters (both numbers and alphabets). I find whereever there is an E in the string, Excel probably confuses to be an error and throws out an error number

for example if original is 89151E109 , then Excel is showing as 8.9151E+113

Is there a formula or code to get rid of this ?


No luck finding an answer? You could always try Google.