1. M

    Sum formula & Lookup on another column

    Hi all. I'm attempting match col A with embedded name on column E. so far i have extracted the match, but I want to add up all the values pertaining to the hardcoded list in A:A. also the number after the name is always one space apart from the end of the name. like "dsgdf 100" See below...
  2. R

    Removing Part of Cell/Text

    Hello - Trying to come up with formula that will give me the number from a different cell, but the cell I am trying to reference also had letters, Example: The cell referenced, lets call it A1, contains: '4 yrs'. I would like cell A2 to calculate 4. I tried doing a =LEFT(A1,1), but the issue I...
  3. S

    Count if query

    I am wondering if someone can help assemble a formula. I have a rota and wish to count the amount of people on night shift. Easy enough to separate and total them with a "countif" however, when people mobilise, instead of the usual "NS" for nightshift or "1" for Day shift, we now have the...
  4. L

    countifs starts with

    Hi! I have the formula COUNTIFS(INDIRECT("'"&$E12&"'!$I:$I");"A*") but instead of A* (to say that it should select the values that start with that) I want to put a cell (f4) which says A. How can I do that? Thank you very much
  5. S

    VBA to extract string between 2 special characters (where spec. characters appear twice)

    I'm looking for some VBA to extract the text: 1620011017 from the below string; Inv_1620056384_1620011017_1034258 There could be any number of characters, so I think I would need to define it based on the fact it's the string between the 2 underscores. I think starting from the right of the...
  6. A

    Using mid/len/left/right/find

    I need excel formulas that will help me differentiate each piece of these cells. Here are examples of the cells Elephant12-8Wb CAT193-45M I already have the formula to get Elephant and CAT on their own. But then I need formulas to get 12 and 193, 8 and 45, and Wb and M on their own. Thank you...
  7. D

    Unable to convert date+time (yy/mm/dd hh:mm) format to excel date

    I have the following column with dates, that I need to convert for excel to recognize them as dates: 21/02/12 15:34 21/02/15 21:18 21/02/17 22:06 21/02/15 21:37 21/02/15 16:05 21/02/17 20:53 21/02/17 00:55 21/02/17 02:34 21/02/19 05:31 I have tried using DATEVALUE, TEXT...
  8. C

    VBA copy LEFT function / formula down all cells in column

    Hi guys! Long time reader, first time poster. Getting very frustrated with my VBA so any help is appreciated. In my worksheet "Paste Data", I have a string of text in column G and need to use the LEFT formula to find and copy the text before the character " - " into column H. My VBA code...
  9. P

    Insert dashes within character string, within an array.

    ISO VBA command to insert a dash (hyphen) as the 5th character from the left, and 6th character from the right, in a string of varying length, within an array. For example... Column Z contains the following list of values: MSEAVIMA0013S MSEAVIRM0016S MOLPVIRM0017S MOLS130012S MSEAVIRM0011S...
  10. T

    Need help nesting a VLookup inside an index, I think?

    Hello all, I've got a problem with a table I set up. Here is the reference table: 2019-20 2018-19 2017-18 2016-17 2015-16 2014-15 2013-14 2012-13 2011-12 2010-11 2009-10 2008-09 Division # Div Name Division OH Division OH Division OH Division OH Division OH Division OH Division OH...
  11. M

    VBA - Need help formatting a row range based on string or format in column A

    I have the code below that works. It searches column A looking for "00" as the left most characters and then formats that cell with white text and black interior. Dim cel As Range For Each cel In Range("A6:A300") If Left(cel.Value, 2) = "00" Then cel.Font.Color = vbWhite cel.Interior.Color =...
  12. R

    Extract more than 2 words into various cells

    I have a string of text in Google Sheets - I would assume is the same formula I can get the first 2 words - but not sure how to expand to get 3rd + In Cell F2 I have typed: (no bold) Wall panels for interior wall FIRST WORD =LEFT(F2; SEARCH(" ";F2;1)-1) SECOND WORD =MID(F2; SEARCH(" ";F2) +...
  13. tourless

    Removing the Last Two...

    Hi Folks, I know this has been asked a thousand times and I'm trying to implement some of the solutions I've found but I just can't seem to get this to work. I have values in column I that four decimal places deep and I simply want to remove the last two characters. The problem I'm noticing...
  14. L

    LEFT() versus --LEFT()

    Hello, I am using this formula --LEFT() instead of LEFT(), and I get different results, but I do not actually understand why. Can anybody explain what the -- does to the formula?
  15. mzainims

    How to seperate into firstname, secondname etc...

    I've got this nagging situation/s. In my excel files I've this: examples in the tables are as below: Names FirstName MiddleName LastName Roberto Engelesias Darwin Subramurthy s/o Thirumurthy Abdool Kassim ibnu Murshid Azmi Bin Abdullah How do I seperate it into...
  16. E

    Cannot use "Right" Function. Apparently meaning has changed.

    I have a large VBA project I'm working on that uses the "Right" function a lot. All of a sudden it gives me an error message of... "Compile Error Wrong number of arguments or invalid property assignments." When I try to compile, with the "Right" Function highlighted. What's really strange is...
  17. L

    conditional formatting - bar based on value

    Hi I created a conditional formatting based on the value of the cell excel would insert bar as below. The bar can move from left to right or right to left. My question can I create similar bar but move from bottom to top? is that possible? Thank you very much
  18. willow1985

    LEFT function of a certain word by a certain amount of characters

    Hello, I am looking for a formula that can find a certain work and return only 10 characters to the left of that word. For example OTHER INFORMATION.... FOR PO# 55222 FROM COMPANY Left of "From Company" I want it to return the PO# 55222 Any help would be appreciated Thank you! :) Carla
  19. J

    LEFT() for each rows in a single cell?

    The following data is in a SINGLE cell. I'm trying to get the phone number from each line and output it as just a list of phone numbers in a single cell. FROM THIS: <tbody> (734) 435-8519 < Sent TXT 14/25/09 @ 08:54 (734) 917-1416 < Sent TXT 14/25/09 @ 08:54 (734) 742-6437 < Sent TXT 14/25/09...
  20. G

    Extract text between a character and first space left of that character.

    Hi, The title says it all. I'm trying to extract the text between a character (character is "%") and the first space to the left of that "%" character. I figure it would need to include the left and find functions but I am having a hardtime figuring out how to put it all together. Any help...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back