# Search:

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

Page 1 of 6 1

1. ## Thread: Data Validation - 3 Letters + 2 Numbers

by Rick Rothstein
Replies
4
Views
21

### Re: Data Validation - 3 Letters + 2 Numbers

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

by Rick Rothstein
Replies
4
Views
21

### 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. ## Thread: Data Validation - 3 Letters + 2 Numbers

by Rick Rothstein
Replies
4
Views
21

### 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. ## Thread: Add next sequence number in next blank row cell only..Excel VBA program

by Rick Rothstein
Replies
17
Views
755

### 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,...

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. ## Thread: Converting sequential numbers in one column to ranges in two columns

by Rick Rothstein
Replies
8
Views
164

### 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. ## Thread: Converting sequential numbers in one column to ranges in two columns

by Rick Rothstein
Replies
8
Views
164

### 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. ## Thread: Insert row question

by Rick Rothstein
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...

Dim Arr As Variant...
9. ## Thread: Converting sequential numbers in one column to ranges in two columns

by Rick Rothstein
Replies
8
Views
164

### 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. ## Thread: InStr() within a Range, mistmatch error

by Rick Rothstein
Replies
5
Views
100

### 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. ## Thread: InStr() within a Range, mistmatch error

by Rick Rothstein
Replies
5
Views
100

### 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. ## Thread: Add next sequence number in next blank row cell only..Excel VBA program

by Rick Rothstein
Replies
17
Views
755

### 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. ## Thread: Add next sequence number in next blank row cell only..Excel VBA program

by Rick Rothstein
Replies
17
Views
755

### 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. ## Thread: TRIM in VBA on a Range

by Rick Rothstein
Replies
14
Views
242

### Re: TRIM in VBA on a Range

Interesting... well atleast Microsoft is maintaining backward compatibility.
15. ## Thread: TRIM in VBA on a Range

by Rick Rothstein
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. ## Thread: TRIM in VBA on a Range

by Rick Rothstein
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. ## Thread: Converting sequential numbers in one column to ranges in two columns

by Rick Rothstein
Replies
8
Views
164

### 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. ## Thread: Number formatting into Sets of 3

by Rick Rothstein
Replies
14
Views
275

### Re: Number formatting into Sets of 3

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

by Rick Rothstein
Replies
28
Views
1,036

### 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. ## Thread: Number formatting into Sets of 3

by Rick Rothstein
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. ## Thread: Excel macro to find words in all caps then surround them in an html tag (e.g., <b> and </b>

by Rick Rothstein
Replies
28
Views
1,036

### 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. ## Thread: Inserting Character From End of String

by Rick Rothstein
Replies
3
Views
66

### Re: Inserting Character From End of String

Here is a formula...

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

by Rick Rothstein
Replies
2
Views
55

### Re: Return Only Different Words In Sentence

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

by Rick Rothstein
Replies
4
Views
67

### 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)

by Rick Rothstein
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