Adapted from [Dynamically Change A Pivot Table's Data Source Range With This VBA Macro Code][1]:

1. The <code>[PivotTable][pt].[SourceData][sd]</code> property can be set  which is set via the [`ChangePivotCache`][cpc] method.
2. To create a new [`PivotCache`][pc], call <code>[ActiveWorkbook][ab].[PivotCaches][pcs].[Create][c]</code>
3. You'll need to pass in a `SourceType` and a [`Range`][r] as `SourceData`. 
4. Finally, once updated, make sure to call [`RefreshTable`][rt] to apply the changes.

Here's an example that will automatically find every pivot table in your workbook and update it.




    Sub AdjustPivotDataRange()
        Dim pt As PivotTable, pc As PivotCache
        Dim dataSheet As Worksheet, ws As Worksheet
        Dim startPoint As Range, dataSource As Range, newRange As String
    
        ' get worksheet with data
        Set dataSheet = ThisWorkbook.Worksheets("Sheet1")
    
        ' Dynamically Retrieve Range Address of Data
        Set startPoint = dataSheet.Range("A1")
        Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell))
        newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)
        
        ' create new PivotCache
        Set pc = ActiveWorkbook.PivotCaches.Create( _
                   SourceType:=xlDatabase, _
                   SourceData:=newRange)
    
        ' loop through all tables in all sheets
        For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
    
                ' update pivot source and refresh
                pt.ChangePivotCache pc
                pt.RefreshTable
    
            Next pt
        Next ws
    
    End Sub

Just replace `"Sheet1"` with wherever your datasource is located.  
[1]: http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range
[pt]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable
[sd]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable.SourceData
[pc]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotCache
[cpc]: https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable.ChangePivotCache
[ab]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.ActiveWorkbook
[pcs]: https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotCaches
[c]:   https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create
[rt]:  https://learn.microsoft.com/en-us/office/vba/api/Excel.PivotTable.RefreshTable
[r]:   https://learn.microsoft.com/en-us/office/vba/api/Excel.Range(object)

Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time?

All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range.

Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand.

Any suggestions?

Updating data source on multiple pivot tables within Excel

Borrowing from [Excel VBA How to detect if something was pasted in a Worksheet][SO]. The `Workbook_SheetChange` event will fire for any change event on the page, including a paste.

From inside this event, you can then check if the last change was a paste by looking at the most recent entry in the Undo List History:



    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Dim lastAction As String
      
      ' Get the last action performed by user
      lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
    
      ' Check if the last action was a paste
      If Left(lastAction, 5) = "Paste" Then
        
        ' Do Stuff Here
        
      End If
    End Sub

[SO]: https://stackoverflow.com/q/27818152/1366033

I need to detect paste command of excel. Is there any work around which can tell us when user click the paste on the menu pop up from the left mosue button click. This is required me to execute a procedure if user click the paste menu item. Any help would be appreciated.

Regards,
Amit

how to detect paste event in excel

It's not particularly elegant, but this will most likely do the trick for you.  Just edit some of the hardcoded values like sheetname and make sure that you don't have more than 99 columns or 999 rows.  




    Public Sub FormatRowsInDateColumns()
    Dim header As String
    
    Worksheets("Sheet1").Activate
    
    'loop through columns
    For col = 1 To 99
    
        'check if header cell contains word date
        header = Cells(1, col).Value
        If InStr(1, header, "date", vbTextCompare) <> 0 Then
        
            'convert cell values to string
            For Row = 1 To 999
                'Formats value and perserves as text with an apostrophe
                Cells(Row, col).Value = "'" & Format(Cells(Row, col).Value, "yyyy/mm/dd")
            Next Row
          
            'set column format as text
            Columns(col).NumberFormat = "@"
        End If
    Next col
    
    End Sub

I am trying to create a macro that can find a particular string in a header, then format the cells in that column.  For an example, I have a header called "Purchase Date", "Cap Date", and "Exp Date".  I want to be able to find the first instance of "Date" used, format them as text, then find the next occurance and format, etc.

I have created one that will find only the first instance, then not look for any further.  Any idea?  I have looked up "Find" and "After", but cannot get them to function correctly.

Thanks for any help.

Excel VBA Macro Find and Format

Try This:

Open up your excel document and hit <kbd>Alt</kbd>+<kbd>F11</kbd> to bring up the VBA editor, insert a new module (if one doesn't already exist), open it up, and paste in the following code (they're custom user defined functions that we'll use in a little bit)


```vb
Function LEFTDELIMIT(ByVal text As String, ByVal delimiter As String)
    Dim position As Integer
    Dim leftText As String
    position = InStr(1, text, delimiter, vbTextCompare) - 1
    leftText = Left(text, position)
    LEFTDELIMIT = leftText
End Function

Function RIGHTDELIMIT(text, delimiter)
    Dim position As Integer
    Dim rightText As String
    position = Len(text) - Len(delimiter) - InStr(1, text, delimiter, vbTextCompare) + 1
    rightText = Right(text, position)
    RIGHTDELIMIT = rightText
End Function

Function NOERROR(text)
    If IsError(text) Then
        NOERROR = ""
    Else
        NOERROR = text
    End If
End Function
```

I'm guessing at this point that all of the text for your quizzes is in A1.  Go ahead and delimit that cell by commas as I specified earlier to get each question in its own column.  Since we want each of those questions to occupy its own row, highlight all of row 1 and copy and then paste special into A2 and select the option to transpose the values.  Now each question has it's own row.  Now what we'd like to do it give each answer choice its own column.  We can use the custom functions from before which allow you to get all the text to the left or right of a custom delimiter.

If the value of A2 is

* 1.Government as an art of governing refers to the process of A.ruling people in the society b.establishing political parties C. providing free education D. acquiring social skills

Then we'll fill out the other columns with the following code:

* B2: `=LEFTDELIMIT(A2,"A.")`
* C2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"A."),"B."))))`
* D2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"B."),"C."))))`
* E2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"C."),"D."))))`
* F2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"C."),"D."))))`
* G2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"D."),"E."))))`

This is making certain assumptions about how the incoming data is formatted. If this doesn't work, please show all of you work in an excel file that you can upload and distribute through any online file sharing host to get a better look at what particular errors might be tripping you up

I've got text file containing MCQ quizzes. Currently I have to edit all the quiz questions with delimiters (e.g TABS) before importing them into excel.  

I need an automated way to format the imported file into these columns:

```none
QuestionId, ExamType, Year, Subject, Question, Answer1, Answer2, Answer3, Answer4, Answer5, CorrectAnswer, Image 
```

without having to manually edit the text first.

Here's an example of the text I'm currently importing into excel.

```none
1.Government as an art of governing refers to the process of A.ruling people in the society b.establishing political parties C. providing free education D. acquiring social skills, 2. An essential feature of a State is A. availability of mineral resources B. developed infrastructure. C an organized system of laws D. developed markets.
```

I'd like to fit example 1 and 2 into the columns above. I have zipped up what I've been doing so that you can have a look.  I also included the raw quiz data so that you can have an idea what it is that i'm trying to force format 



force formatting imported text file in excel

You can `Index` all of your data and then `Match` rows to the subset names and pull over each column.

For example, it would look something like this:

    =INDEX(All!A1:F4,MATCH(Subset!A2,All!A1:A4,0),2)

Let's break this down:

The index function takes the following arguments:

    Index(Array, Row_Number, Column_Number)

The array will be all the data you have, the row number we'll use a match function (below) to find which row to get data from, and the column number represents which column from the index to pull information from (we'll keep incrementing this to get all the columns).

To return the correct row, we'll use match

    Match(Lookup_Value, Lookup_Array, Match_Type)

For lookup value, we want to point it to the current row of the subset list, the lookup array will be all the names available from the index we defined previously in the master list, and the match type will equal 0 (for exact match)

Take a look at this [excel document](https://skydrive.live.com/redir?resid=9935281CD314F220!25984&authkey=!AIwnjuwPr8gGloQ)


So I have an excel dataset of participants that are possibly eligible to partake in a study  that consists of their names, age, telephone number, etc... like:

Name | Age | Phone | Email | Address | Studies Participated in 

I also have a column of names of participants that are eligible for a new study; it is a subset of the data in the name column.

What I need to do is select all of the participants whose names are in the subset column, and copy and paste all of their information in all of the columns into new columns. 

I have tried something like, selecting an empty column and using =IF($Name-column = $Subset-column, First-Column:Last-Column, "") But this only checks if the name in each cell of the Name Column matches the name in the Subset column. What I need is something that checks if any of the names in the Name column match those in the Subset Column, and if they do, then to copy that row to a new worksheet.

Filtering Dataset by values in one column

Here's an extension method to output to any DataTable to a csv (which excel can open)



	Imports System.Text
	Imports System.IO
	Imports System.Runtime.CompilerServices

	Module ExtensionMethods

		<Extension()> _
		Public Sub OutputAsCSV(ByVal dt As DataTable, ByVal filePath As String)
			Dim sb As New StringBuilder()
					
					'write column names to string builder
			Dim columnNames As String() = (From col As DataColumn In dt.Columns Select col.ColumnName).ToArray
			sb.AppendLine(String.Join(",", columnNames))
					
					'write cell value in each row to string builder
			For Each row As DataRow In dt.Rows
				Dim fields As String() = (From cell In row.ItemArray Select CStr(cell)).ToArray
				sb.AppendLine(String.Join(",", fields))
			Next
					
					'write string builder to file
			File.WriteAllText(filePath, sb.ToString())
		End Sub

	End Module

I have a datagrid which populates a datatable, I want to export the DataTable to excel on click of a button. I am using MVVM for this application. So is it ok to implement the export feature in my view? 

Secondly, as i am using xaml and desktop application, how do i capture the grid and its details.

Can any one suggest me some pointers? I am new to this.

Thanks,
Sagar

DataTable to excel conversion

You can use [**`CountIf`**](http://office.microsoft.com/en-us/excel-help/countif-HP005209029.aspx).  Put the following code in B1 and drag down the whole column

    =COUNTIF(A:A,A1)

It will look like this:

![enter image description here][1]


  [1]: http://i.stack.imgur.com/KN8bz.png

I have a column with time data like this:

    17:27:31
    17:27:32
    17:27:32
    17:27:33
    17:27:33
    17:27:34
    17:27:34
    17:27:34
    17:27:35
    17:27:36

I want to count how many times each value repeats:

    Value      Count
    17:27:31   1
    17:27:32   2
    17:27:33   2
    17:27:34   3
    17:27:35   1
    17:27:36   1

How can I do such a thing?


How many times does each value appear in a column?

As Tim has pointed out, the [`MIN(number1,number2,...)`][min] function in excel works perfectly fine on dates in order to determine the earlier date.  Also, here's some more info on [adding days][days].

So you can use something like this:

    =MIN(A2:B2)+30

Which should like this:

![demo][demo]



[min]: http://office.microsoft.com/en-us/excel-help/min-HP005209176.aspx
[days]: http://office.microsoft.com/en-us/excel-help/add-dates-HP003056112.aspx
[demo]: http://i.imgur.com/JknjiPb.png

I created a worksheet where there are two dates:

1. `AppDate` 
2. `ActionDate` 

I need to calculate a new `ActionDate` that is 30 days after the earlier of the `AppDate` or `ActionDate` that is input.  

For example, I have an `AppDate` of 10/30/13 and an `ActionDate` of 11/1/13.  I need to calculate off of the 10/30/13 date to create the new action date (25 days out) and the 30 days from date.  

The first app and action dates may be the same or may be different.

Is there a way to do this?


Formula to calculate time frames

Sort normally by highlighting the column and Hit <kbd>Alt</kbd> + <kbd>A</kbd> + <kbd>SA</kbd>

-----

## Update:

[`'Excel sorts alphanumeric text left to right, character by character'`][AlphaNumericSort], so it may not properly order your numbers if combined into a single cell.  You should create an extra column with just the two letter code that you want to use for sorting by using `=LEFT(A2,2)` and copying all the way down.  Then do a multilayered sort by clicking the sort button, sorting first on the two digit code alphabetically and second on the number column:

![example](http://i.imgur.com/5M4WSCr.png)

[AlphaNumericSort]: http://support.microsoft.com/kb/322067#MT0

I have two columns I want to sort.  I want it to look exactly like below with Column A first sorted by its first two letters, then by Column B.


    Column A     -    Column B
    AB - Info         3339876
    AB - Data         3339877  
    AB - Data         3339878 
    AC - Info         3339123
    AC - Data         3339124
    AC - Info         3339125
    AD - Info         3339456
    AD - Info         3339457
    AD - Data         3339458

The first two letters of Column A are the MOST important and must be sorted by them first.  The information after the first two letters of Column A is irrelevant and does not matter. It is much more important that the Column B # data to be sorted in ascending order second (after the first two letters of column A)

Sorry for the confusion. Hopefully that clears things up.

Excel VBA - How to sort by first two letters of cell?

I'm trying to use a [custom format][custom format] to a group of cells so that decimals are permitted, but whole numbers are shown when used.

For example, here's some sample input and desired output:

    2   --> 2 day(s)
    1.5 --> 1.5 day(s)

If I use a number format with a decimal place, I always get the decimal point.

    ###.# " day(s)";;@
    2 --> 2. day(s)

If I don't use a decimal place, then it always rounds.

    ### " day(s)";;@
    1.5 --> 2 day(s)

Is there a way to conditionally add the decimal point?


[custom format]: http://office.microsoft.com/en-us/excel-help/create-a-custom-number-format-HP010342372.aspx

Hide decimal for a whole number with Excel Custom Formatting

It sounds like what you want to do is get the value of everything to the left of the `/` symbol in a particular field. To do that, you can use the following formula:

    =LEFT(A1,FIND("/",A1)-1)

where `A1` is the cell you want to change.

Here's how it works:

`Find("/", A1)` finds the first instance of the "/" in the text in `A1` and returns it's position.

Then `Left(A1, <#>)` takes all the characters to the left of position calculated by the previous expression.

I want to trim a row in excel which contains the following values:

    row a                row b
    texas/dallas         5
    texas/austin         10 
    california/sf        5
    california/la        20

to 

    row a                  row b
    texas                  5
    texas                  10
    california             5
    california             20

Excel formulas on triming a cell value

There are certain conventions in order to force a literal to a specific type from a generic (default) type: http://msdn.microsoft.com/en-us/library/dzy06xhf.aspx

Which leads to this code:

    Dim secondsInAWorkWeek As Long
    
    Let secondsInAWorkWeek = 60& * 60& * 8& * 5&

or:

    Const DAYS_IN_WEEK = 5&
    Const HOURS_IN_DAY = 8&
    Const MINUTES_IN_HOUR = 60&
    Const SECONDS_IN_MINUTE = 60&

    Dim secondsInAWorkWeek As Long

    Let secondsInAWorkWeek = _
         DAYS_IN_WEEK _
       * HOURS_IN_DAY _
       * MINUTES_IN_HOUR _
       * SECONDS_IN_MINUTE

A lot more to write, true, but is type safe, barely needs any more explanations/comments, and it will be easy to change when they'll vote for 10 hours workdays. :-)

Simple question that I can't find anywhere.  When I'm doing arithmetic, it seems the natural type is to treat value as 16Bit Integers.  

I'm trying to save the result of `60 * 60 * 8 * 5` into a long, but I get an `Overflow` error  before it even has a chance to save the number as a `Long`:



    Dim secondsInAWorkWeek As Long
    secondsInAWorkWeek = 60 * 60 * 8 * 5

Long should happily store anything up to 2<sup>31</sup> = 2,147,483,647

How can I perform the multiplication safely to convert into a long

Set Long Variable in VBA

## Update with Array Function

**First Attempt**:

So at first I had thought you could just add a second condition to the Array Function like this:

    =AVERAGE(IF(AND($C$2:$C$9={"LA","SF","POR","SEA"},$B$2:$B$9={"North"}),$D$2:$D$9))

Where the `AND` would require each item it be true, but this appears **not to have worked**... and array functions are tricky beasts to debug.

**Second Attempt**:

Instead, what you can do is *extend the `IF` function* with a second IF.  So in the event that the first one is true, the value should return another IF that checks a second criteria.  Only if it meets both criteria, return the score, otherwise it will still return nothing , which won't get picked up by the average function.

So the final thing should look like this:

    =AVERAGE(IF($C$2:$C$9={"LA","SF","POR","SEA"},IF($B$2:$B$9={"North"},$D$2:$D$9)))

### Named Array

Since you are unable to create a column to store the data related to which cities live in the west, it would be best if you had some way to easily update this information instead of hardcoding it throughout your functions.  One thing you can do to is define a name to reference later.  

Just go to Formulas Ribbon > Defined Names Section > Name Manager > New

Then create the array of western city values as a named parameter to be used later

![Named Value](http://i.imgur.com/tO1JPlT.png)

Then you can use it in a formula in place of the array like this:

    =AVERAGE(IF(C2:C9=WestCities,IF(B2:B9="North",D2:D9)))

![Named Usage](http://i.imgur.com/guYUII8.png)


## With PivotTable

Here's my suggestion:

First, add a column for East/West, so you're not stuffing data into a formula

![Data](http://i.imgur.com/jp7ywjN.png)

Then add a pivot table and it will do all you calculations for you.

Set it up like this (make sure to aggregate by Average (not sum):

![Pivot Setup](http://i.imgur.com/rtpfVhN.png)

And it will look like this:

![Pivot](http://i.imgur.com/TxvLhWV.png)

If you want to keep your original formatting, you can just run the formulas off of your pivot table like this:

    =GETPIVOTDATA("Score",Sheet4!$A$3,"E/W","West","S/N","North")

![Pivot Formula](http://i.imgur.com/t3SaXsY.png)

## With West Column

Also, if you had the ability to add in a column for west, you could easily check this with AVERAGEIFS with multiple criteria like this:

    =AVERAGEIFS(D2:D9,A2:A9,"West",B2:B9,"North")

![AverageIfs](http://i.imgur.com/xvh4g59.png)



I am trying to find a formula to check conditions in two different columns and then calculate the average of the corresponding values. The following example reflects what I would like to achieve. Let's say I want to find the average of North-western cities. 

I already identified the average of the western cities with an *array function* like this:

    =AVERAGE(IF($C$3:$C$10={"LA","SF","POR","SEA"},$D$3:$D$10))

Then hit <kbd>Ctrl</kbd> + <kbd>Shift</kbd> + <kbd>Enter</kbd>

Because of how my data is organized, this previous formula is the only way I will be able to determine (manually somewhat) whether a city is located on the east or the west coast.
 
Now I'm missing the part that would say "*And also if the previous column says 'north'* "

![enter image description here][1]


  [1]: http://i.stack.imgur.com/l1jRxm.png

Multi column if statement - Excel

You can use the `INDEX` function to get the information from a particular set of coordinates.

For example, in this case, we'd get the value for the cell in the 5th row and the 1st column of the selected area:

![Index](http://i.imgur.com/i6uWzkT.png)

If we want to dynamically find those values, we can use `MATCH` to find the column or row number corresponding to our value like this:

    =INDEX($B$2:$D$6,5,MATCH($A10,$B$1:$D$1,0))

![Match](http://i.imgur.com/Vqm2rCt.png)

In this case the column number is dynamically pulled based on the item you're on.  The row number is hardcoded as row 5, but you can use the same technique to pull in a row number

The end result should look like this:

![Result](http://i.imgur.com/qN2WpRI.png)


Mark items based on criteria

### HTML Code

I'd try working out the code that manipulates the Document Object Model (DOM) in javascript in a web browser so you can make use of better web based debugging tools.  

There are **several issues here** that a console or debugger could help out with:

* You want to get the element ID `myPoints` but in HTML it's just called `points`
* You want to get the element by ID, but you've only set the name property - 
* As long as `name` is unique to the element, you don't need to search for a `td` first
* As you can see from `<input></input>`, input elements do not have `innerText` (the text inside the `><`). Instead they have a `value` attribute
* The element exposes it's attributes and other data through the properties on the object itself.  So you can check the input's value by just looking at `.value`

Here's a javascript example of what you're trying to do:





    var value = document.getElementsByName("points")[0].value;
    console.log(value);



    <input type="text" disabled="disabled" value="300" name="points" />
        



Open the console (<kbd>F12</kbd>), and you should see `300`

### VBA

To convert it to VBA code for Excel, just make sure you uses parentheses `()` for VB arrays instead of square brackets `[]` for JS arrays:



    myPoints = Trim(Doc.getElementsByName("points")(0).Value)

That should work just fine.

### References

Since I'm not sure at what point you're failing in VB, also make sure you have all the proper web references in place in your VBA script.

Go to Tools > References > and add "Microsoft HTML Object Library" and "Microsoft Internet Controls":

![References][1]


### Demo

I created a [**demo in plunker**][2] so there would be a live site to go against instead of example.com.

Paste the following code into excel and everything should work fine:



    Public Sub GetValueFromBrowser()
        Dim ie As Object
        Dim url As String
        Dim myPoints As String
        
        url = "http://run.plnkr.co/plunks/6UTb9kHRZ363Ivhh2BPE/"
        Set ie = CreateObject("InternetExplorer.Application")
        
        With ie
          .Visible = 0
          .navigate url
           While .Busy Or .readyState <> 4
             DoEvents
           Wend
        End With
        
        Dim Doc As HTMLDocument
        Set Doc = ie.document
        
        myPoints = Trim(Doc.getElementsByName("points")(0).Value)
        Range("A1").Value = myPoints
    
    End Sub

**Output**:

![output](http://i.imgur.com/d0VU3RV.png)

[1]: http://i.imgur.com/3INl86i.png
[2]: http://plnkr.co/edit/6UTb9kHRZ363Ivhh2BPE?p=preview

I am having difficult to retrieve value `300` from the input named `points`.

Here's my HTML and VBA code.

**HTML**:



    <td id="myPower_val_9" style="visibility: visible;">
        <input type="text" disabled="disabled" value="300" name="points"></input>
    </td>

**VBA**:



    Dim ie As Object
    Dim myPoints As String

    Set ie = CreateObject("InternetExplorer.Application")
  
    With ie
      .Visible = 0
      .navigate "www.example.com"
       
       While .Busy Or .readyState <> 4 
       DoEvents
       Wend

    End With
    
    Dim Doc As HTMLDocument
    Set Doc = ie.document

    myPoints = Trim(Doc.getElementsByTagName("td")(0).getElementById("myPoints").innerText)
    Range("A1").Value = myPoints

Get value from web document input element with VBA

You can get the number of characters in the cell and then compare that to the number of characters in the cell if you substituted out all the commas with empty spaces:

    =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

<sub>This is also a neat way to get word counts in excel by counting the number of spaces.</sub>

A client has an Excel file that needs to have some names scrubbed. In particular, we're trying to isolate suffixes on names (e.g., Jr., Sr., III, etc.) Names are ALWAYS formatted `LastName, FirstName, Suffix` in the cell, and I am trying to count the number of commas in a cell. If the cell has more than one comma in it, I can identify that cell as having a name suffix. 

However, all of the `COUNT` functions in Excel count instances of CELLS, not characters within cells. Is there a function that counts occurrences of specific characters in a cell and returns that count?

Is there an Excel function to count occurrences of a certain characters in a cell?

To answer the *why*, it's because [accountants put negative numbers in brackets for readability][1]

Unfortunately, this is one of the excel *feature/bugs* that helps some folks and frustrates others.  When opening a file or pasting content, excel will immediately and always try to parse any values into formats it deems appropriate, which can mess up data like:

* **Zip Codes / Tel. # → Numeric**: `05401` → `5401`
* **Fractions → Dates**: `11/20` → `Nov, 20th YYYY`
* **Std. Errors → Negative Numbers**: `(0.1)` → `-0.1`

For some workarounds , see [Stop Excel from automatically converting certain text values to dates][2]

Once the file is open/pasted, the damage is already done.  At that point, your best bet is:

* Updating the field and displaying as text (appending with `'`) to prevent re-casting
* Formatting the field if the operation wasn't lossy and is just presenting the info differently
* Running a clean if/else to pad or other convert your data based on the identified errors

Specific to displaying values back in parens, if excel is converting them and treating them like negative numbers (which may or may not be the appropriate way to actually *store* the data), you can apply a [different format to positive and negative numbers][4] to wrap back in parens.

[![Format Cells][3]][3]


  [1]: https://www.quora.com/Why-do-accountants-display-negative-numbers-in-brackets
  [2]: https://stackoverflow.com/q/165042/1366033
  [3]: https://i.stack.imgur.com/2Kzn1.png
[4]: https://www.datanumen.com/blogs/numbers-parentheses-will-change-negative-numbers-worksheet/

A colleague and I encountered a behavior in Excel which isn't clear to us.

**Background**: 
We have a tool which converts an Excel sheet into a table format. The tool calculates the formulas which are in excel and replaces variables inside it with specific values.
The excel tool is used by one of our customers who use values like `(8)` or `(247)`.
These Value are automatically translated by excel to `-8` or `-247`.

**Question**:
I saw that many people want to display negative numbers in parentheses. But why would Excel change values in parentheses to a negative number?

I know that I could simply change the cell config to text and this would solve the problem but I wonder if there is a reason for the behavior, since there seems to be no mathematical reason for this.

Why do Excel values in parentheses become negative values?

From the article [Getting the 2nd matching value from a list using VLOOKUP formula][1],  you can create a helper column to affix the instance number of each value, to create unique id's.

For example, in Column C, add the following function:

    =A1&"-"&COUNTIF($A$1:A1,A1)

> **Note**: The relative reference on the count range will cause the applicable range to grow as it is dragged down.  The count of the items matching that cell in a range containing only that cell should always be one.  As it gets dragged down to include other cells, it will increment accordingly.

Then add the same thing in Column D to get the instances of cells in Column B:

    =B1&"-"&COUNTIF($B$1:B1,B1)

Finally, do the math you want to do in Column E like this:

    =IFERROR(MATCH(C1,D:D,0),0)

![Screenshot][2]


  [1]: http://chandoo.org/wp/2010/11/10/vlookup-second-value/
  [2]: http://i.stack.imgur.com/0tYef.png

I'm using excel and have two columns (A & B) with values

I want to search for each value in Column A and return the position in Column B.
I'm using this formula: 

    IFERROR(MATCH("Values in Column A";"Array = Column B";0);0)

The results are:  



    Column A  | Column B  | Column C
      1       |   4       |   2
      2       |   1       |   3
      3       |   2       |   4
      4       |   1       |   1
      1       |   2       |   2
              |   3       | 

It works fine if it doesn't encounter repeated values. However, I want it to encounter repeated values, so the formula should ignore the ones it was encountered before and go through the others. So the correct result should look like this:  



<pre><code>Column C  
  2  
  3  
  5  
  1  
  <b>4</b></code></pre>

Can you help me on this? Is there a VBA routine for this?