Jump to content
By fans, for fans. By fans, for fans. By fans, for fans.

Excel gurus


Red Kent

Recommended Posts

Right.

 

I have a column of cells A1:A50.

 

If the letter S appears in any of those cells I want the total to incriment x 11. ie 1 x S = 11, 2 x S =22, 3 x S = 33 etc etc.

 

The main thing is there could be other letters and numbers inserted into those cells and I dont want to take them into account - only the letter S.

 

Anyone out there than can help please ?

Link to comment
Share on other sites

Works for me. Hope this helps etc etc

 

Type 'JESUS CHRIST SUPERSTAR' into A6 type '=LEN(A6)-LEN(SUBSTITUTE(A6,"S",""))' into B6, and it returns 5, which is what you'd expect.

 

I know but do I have to do that for every cell I want ?

 

I do want to include cells A1 to A50 in the formula.

Link to comment
Share on other sites

I know but do I have to do that for every cell I want ?

 

I do want to include cells A1 to A50 in the formula.

 

Ahh. So you want a total of ALL instances of the letter 'S' for all 50 cells. I'd just copy and paste the formula for all 50 cells and do a SUM of the values calculated at the bottom.

Link to comment
Share on other sites

thats what I'd do.. or concatenate all 50 and run the formula against the final text

 

 

and a quick concatenate short cut for you

 

 

i your data is all in one column (say a1 to a50) then save this macro and run it.

 

Sub concat()

Dim Start As Integer

Dim Finish As Integer

Dim Column As Integer

Start = InputBox("Please enter row number of first variable")

Finish = InputBox("Please enter row number of last variable")

Column = InputBox("Please enter column number of data")

For f = Start To Finish

Cells(f, Column).Select

code = ActiveCell.Text

sqlstring = sqlstring & code

Next f

finalcell = InputBox("Enter cell you want result in")

Range(finalcell).Select

ActiveCell.FormulaR1C1 = sqlstring

End Sub

 

 

 

then you just put in the first row (1) the last row(50) the column to look at (a=1, b=2 etc) and the cell to put the result in (eg b2)

 

The macro will combine everything into one cell and you can run the first formula I posted against the one cell. Again you will need to run the first formula for lower and upper case and add them for the total.

 

 

anything else I can do to make your work day/ night even shorter...?

Link to comment
Share on other sites

Ahh. So you want a total of ALL instances of the letter 'S' for all 50 cells. I'd just copy and paste the formula for all 50 cells and do a SUM of the values calculated at the bottom.

 

Yep, that's what I did and it worked wonders if not time consuming.

 

Thanks to all.

Link to comment
Share on other sites

Yep, that's what I did and it worked wonders if not time consuming.

 

Thanks to all.

 

Time consuming? If you have your text in columns A1:A50 and type the formula into B1, then all you need to do is click the bottom-right of the cell and drag the outline to B50 in order to copy-and-paste the formula 50 times. It changes B1 to B2,B3,B4 etc automatically. It takes a split second to do. You didn't type out the formula 50 times did you?

Link to comment
Share on other sites

Time consuming? If you have your text in columns A1:A50 and type the formula into B1, then all you need to do is click the bottom-right of the cell and drag the outline to B50 in order to copy-and-paste the formula 50 times. It changes B1 to B2,B3,B4 etc automatically. It takes a split second to do. You didn't type out the formula 50 times did you?

or even double click it and it will fill all the waydown... that might save you about 3 seconds more..

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...