1. R

    Error during pasting Excel formula consisting of Mid, Len, Search into VBA module

    Hi guys, I'm trying to insert a formula in my code which works in the sheet but not in vba. Running the code below returns a syntax error. I noticed that the errors are due to SEARCH strings containing paratheses for "ENO=" and ",SUB". I've tried changing the paratheses to CHR(34) as well but...
  2. L

    Adding or subtracting 1 from number value in cell that also contains text

    Hello Everyone, I'm looking for a formula that would cause a number value to either increase or decrease by one. Examples of text include: Apr Y0, May Y0, Jun Y1, Jul Y4. The common denominator is that the number is always preceded by " Y". Thanks in advance for all your expertise! Please let...
  3. A

    Trouble getting a Nested If statement to work

    Hi there First post, hopefully I've followed the rules. I've had a good search but can't find anything specific to the issue I am having. It's very basic so I'm surprised it isn't working. I've attached an image that hopefully clearly explains the issue I am having. Basically I'd like to...
  4. 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...
  5. L


    Hi all, I am using a formula which uses LEN and SUBSTITUTE. The formula works (I didn't learn it by building up, I got it from a tutorial), and I understand what LEN and SUBSTITUTE do separately, however, I don't understand what they are doing in this formula. I was hoping anyone would explain...
  6. D

    How to count SPECIFIC text characters with a range of cells

    Hey guys, Can someone assist me in writing a formula for counting how many "I" are in a range of cells. For example I have a range of cells B11:AF12 that have different letters in it to identify the kind of Lead Source we receive. "I" represents Internet. I need to find out how many I's are in...
  7. A

    Summary of name and cocatenate data

    Hi, Hope that someone can help me if this is possible to do in a formula. Sorry that I can't post screenshot of table because I couldn't get the add-in to work. :sad: Name Run Out Tim 6 1 John 5 3 Len 6 3 John 3 4 Len 4 2 Tim 2 3 How i would like to...
  8. kelly mort

    Check if a cell contains same digits

    Hi, I am looking for a way to verify if a cell say A1 contains same digits like 1111, 2222, etc. The Len will always be 4. Thanks in advance
  9. H

    Len if and

    Hi I have set-up the below but the LEN calculation seems wrong as LEN that isn't 15 is matching and doing the workday function. Can anyone see what is wrong please?
  10. B

    IF OR with LEN

    Hello, This on has me baffled and I tried everything I could think of. Longstory short, I'm working with an old windows program that extracts data into anexcel worksheet. There are a ton of rows,but many are useless (like headers, totals, other stuff) Anyway, I did discover that the data I...
  11. N

    IF and Len Function Help

    hello all, I need help with the funtion I am able to do len first and then if function but i cannot get them to work together. I need a function that can take the Length of the numbers in cell B17 and if greater than or equal to 8, display a value of "Cusip" If the length is less than or...
  12. D

    Excel Newbie Here: Trying to get Sum of Occurances for Columns by Cross Referencing Two Worksheets

    My Task is to Summarize the Ingredients By Categories. I have two Worksheets/Tables. The First lists the Categories and their respective Recipes CategoryRecipe <tbody> Category (A) Recipe (B) Cat1 RecA Cat1 RecB Cat2 RecD Cat2 RecC Cat3 RecA Cat3 RecD </tbody> Next is the...
  13. J

    Convert numeric to text

    I have a value in a5 (82700000) Needing it to display in a6 as text 82.7 Million. Could also be 827000000, needs to be displayed as text 8.27 Billion I know it has to do with Len Just cant get there, Any tips or solutions are greatly appreciated. Jack
  14. M

    a text formula that i cant crack!

    I am trying to make a formula which basically says "find the first word that starts with G then choose the word AFTER it" but i cant configure the formula correctly, i am a bit of a novice! i can do it by combining IF statementslike this: =IF(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("G",B4),LEN(B4)),"...
  15. Z

    Formula help to return first name space last name from 2 cells

    Hi I am trying to pull from the sheet below into my cell the first and last name from Job Costing. In the sheet below first name is in C2 and last name is in B2. Then eventually I would scroll down. I looked up =Right Len, but confused its saying maybe I need to do =Left len any help thanks...
  16. W

    VBA - Search string left, offset and remove found string

    Hello wise people of the internet. I'm tinkering with a code in VBA. It's purpose is to look at each cell in a given range. Part 1: Looking at the LEFT side of a string, it searches for "/56-Test1/" And it MUST be the first part of a string. Example: "A/56-Test1/AAA" should be ignored while...
  17. L

    Extracting a SKU from a URL

    Hi, I have the following example URLs: https://viyet.com/andrianna-shamaris-teak-and-bone-inlay-circular-bookcase-sto-49740-27277.html https://viyet.com/janus-et-cie-large-shell-bowl-acc-49959-2600.html https://viyet.com/jonathan-adler-danner-loveseat-sea-45150-33425.html The length is...
  18. M

    How to Add "Skip if Len <2" to VBA Code That Converts CSV to New Rows

    Hi all, Below is my code: Sub RedistributeCommaDelimitedData() Dim xArr() As String Dim xAddress As String Dim Rg As Range Dim Rg1 As Range On Error Resume Next xAddress = Application.ActiveWindow.RangeSelection.Address Set Rg = Application.InputBox("please select...
  19. B

    Add into a formula Right & Len

    My current formula is =SUMPRODUCT(--(A11>='Front End'!B$25:B$72),--('Front End'!C$25:C$72>=A11+"00:15"))+SUMPRODUCT(--(A11>='Combo EE'!B$36:B$69),--('Combo EE'!C$36:C$69>=A11+"00:15")) I'm having an issue because when I copy data in the range C25:C72 and B25:B72 the time now has a date in...
  20. B

    Count Characters Minus One

    I am using Len Function to count number of characters: =LEN(A1) Now, I have instances that I would like to count as one character and not 2. For example, instances like "DZ" or "LJ", if they exist anywhere in the word it should be counted as -1 to the total of characters. I can solve this...

Watch MrExcel Video

This Week's Hot Topics

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