Search:

Type: Posts; User: Rick Rothstein; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.89 seconds.

  1. Re: Data Validation - 3 Letters + 2 Numbers

    Your thread title said 3 letters so that is what I responded to.
  2. Re: Data Validation - 3 Letters + 2 Numbers

    Give this formula a try...

    =AND(LEN(A1)=5,IF(MID("AAA00",{1,2,3,4,5},1)="A",(MID(A1,{1,2,3,4,5},1)>="A")*(MID(A1,{1,2,3,4,5},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5},1))))
  3. Re: Data Validation - 3 Letters + 2 Numbers

    The RIGHT function returns a Text value, not a numeric value, so ISNUMBER will not see the return from the RIGHT function as a number even if it is composed of all digits. However, if you involve a...
  4. Re: Add next sequence number in next blank row cell only..Excel VBA program

    Give this macro a try...

    Sub FillBlanksWithNextSequenceLetter()
    Dim R As Long
    For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = "" Then
    Cells(R,...
  5. Thread: Inquiry

    by Rick Rothstein
    Replies
    7
    Views
    119

    Re: Inquiry

    When you say transferred to rows (plural), do you actually mean "across columns in a single row"? I would note your values are already in rows.
  6. Re: Converting sequential numbers in one column to ranges in two columns

    :confused: Well, I am a little confused now. In your original message, the values after the dot were actually sequential (211, 212, 213, etc.) and you want them condensed to remove the middle of the...
  7. Re: Converting sequential numbers in one column to ranges in two columns

    Yes, if you do not have digits only after the decimal point, then that would affect the calculations Peter and my codes were doing. Please show us before and after data (like you did in Message #1 )...
  8. Replies
    5
    Views
    154

    Re: Insert row question

    For those who might be interested in seeing a VBA macro solution, this one will work so long as the final outputted range is less than 65500+ rows...

    Sub AddMaryAfterLarry()
    Dim Arr As Variant...
  9. Re: Converting sequential numbers in one column to ranges in two columns

    Are you responding to Peter or me?

    Either of us would need to know on which line of code the error occurred on?
  10. Re: InStr() within a Range, mistmatch error

    Yes, the asterisks allow for there to be text either before or after (or both) the text you are searching for. I did that because you tried to use InStr which allows for text to before or after the...
  11. Re: InStr() within a Range, mistmatch error

    If that is what the OP wants, then here is another way...

    MsgBox IIf(Application.CountIf(Range("A1:A200"), "*All Formulas*"), "Yes", "No")
  12. Re: Add next sequence number in next blank row cell only..Excel VBA program

    Will the number of blanks between letters always only be one cell or could there be multiple blank cells between letters that need to be filled in?
  13. Re: Add next sequence number in next blank row cell only..Excel VBA program

    Will any blanks after the last filled cell need to have "AAA" placed in them? If so, what can be used to determine the last row that fills will need to go down to?
  14. Replies
    14
    Views
    242

    Re: TRIM in VBA on a Range

    Interesting... well atleast Microsoft is maintaining backward compatibility.
  15. Replies
    14
    Views
    242

    Re: TRIM in VBA on a Range

    Office 365 does not require the array processing to be induced into text functions? When I run your code line in XL2010, it clears all of the cells which is why I used the IF function call that I did...
  16. Replies
    14
    Views
    242

    Re: TRIM in VBA on a Range

    And, since the range is fixed in size, either of these one-liners will also work...

    Sub TrimRange()
    [A1:A6000] = [IF({1},Trim(A1:A6000))]
    End Sub


    Sub TrimRange()
    [A1:A6000] =...
  17. Re: Converting sequential numbers in one column to ranges in two columns

    Using the same assumptions that Peter made, here is another macro to consider...

    Sub Get_Sequences()
    Application.ScreenUpdating = False
    With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    ...
  18. Replies
    14
    Views
    275

    Re: Number formatting into Sets of 3

    Yikes! I completely missed that!!! :banghead:
  19. Re: Excel macro to find words in all caps then surround them in an html tag (e.g., and

    Hmm! I wasn't aware that Excel's PROPER function didn't handle 's, 't, etc. at the end of a word correctly. Okay, given that, I am not sure how to work this into your code, besides, I am sure you...
  20. Replies
    14
    Views
    275

    Re: Number formatting into Sets of 3

    A little simpler formula...

    =MID(TEXT($A2,"000000000"),3*COLUMNS($B:B)-2,3)
  21. Re: Excel macro to find words in all caps then surround them in an html tag (e.g., and

    Don't use StrConv to change text to proper case (as you point out, it does a terrible job of it), rather, use Application.Proper instead (I believe it handle's the above correctly).
  22. Re: Inserting Character From End of String

    Here is a formula...

    =REPLACE(A1,LEN(A1)-2,0,".")
  23. Re: Return Only Different Words In Sentence

    *** Cross-posted on "Excel Forum" forum ***
  24. Re: Extract a number from a long Cell

    Using your assumptions, this shorter formula should also work...

    =0+MID(LEFT(A1,FIND("+",A1)-1),FIND(";",A1,FIND(";",A1)+1)+1,99)
  25. Replies
    5
    Views
    88

    Re: Date loop

    If the range is really fixed and non-changing, this can be shortened to this...

    Sub temp3()
    [B2:B4] = Date - Day(Date)
    End Sub
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4