LinkBack URL
About LinkBacks
I have about 5000 rows with mixed upper & lower case words. I am trying to get only values if all words are upper-case. I have office 2013.
Can anyone perhaps help?
Return value only if all words are uppercase
I have about 5000 rows with mixed upper & lower case words. I am trying to get only values if all words are upper-case. I have office 2013.
Can anyone perhaps help?
Do you mean if the first letter of the word is a capital or if all letters in the word are capitalised?
Also, how many words in each cell?
Hi TKCZBW
1.No not only first letter of word. It must be where the words are completely in uppercase.
2.It differs, I can't say exactly.
This should do it:
It assumes all your input values are in row 'A', and will then output True/False in Row 'B'.
Sub Test() Dim l As Integer Dim Text As String lastrow = ActiveSheet.UsedRange.Rows.Count For i = 1 To lastrow Text = Cells(i, 1).Value l = Len(Text) For x = 1 To l If Mid(Text, x, 1) Like "[A-Z]" Or Mid(Text, x, 1) Like " " Then Cells(i, 2).Value = "TRUE" Else Cells(i, 2).Value = "FALSE" Exit For End If Next x Next i End Sub
Let me know if it works!
Last edited by TKCZBW; 04-16-2014 at 05:38 AM. Reason: Extraneous variable
It worked beautifully ... thank you so much!
(one day when I'm "all grown-up" I want to be able to do what you did, myself)
Thank you thank you!
Not at all, happy to help!
We all started somewhere and we never stop learning (and forgetting) things.
If I helped, can you mark this post as 'solved' and add to my reputation?
Best,
TKCZBW
Hi Petro, hoe gaan dit daar in die kaap? SA ex-pat here from PE and Klerksdorp
Another option to consider withe be an IF() function build around a simple test to see if all letters in your string are upper case, something like...
=IF(EXACT(UPPER(A1),A1),do-this,do-that)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi FDibbins - dit gaan goed hier in die Kaap! Net warm. Wonderlik ... PE & Klerksdorp..! Ek het familie in beide.
Ek sukkel met die "if" - ek het ongelukkig nie hierdie ondervinding nie, ek wens ek het gehad. Jy gaan my asb moet leiding gee ..."do-this,do-that" hehe... ek het geen idee nie.
=exact(upper(a1),a1)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Martin, that's a much cleaner way than mine.
Thanks for the tip!
hmm ford beat me too it it funny my iphone didnt show all the thread
Petro, an IF() statement is made up of 3 parts, a test, option 1, option2.
So you perform your test, then based on the results of that test, you either do option1 or you do option2.
Based on your question...
if the value you want to return is in B2, and your text is in A2, then maybe something like this...I am trying to get only values if all words are upper-case. I have office 2013
=IF(EXACT(UPPER(A2),A2),B2,"") The "" will return a "blank" cell
Last edited by FDibbins; 04-17-2014 at 09:27 AM. Reason: Thanks for the catch, sktneer :)
Shouldn't it be like this?if the value you want to return is in B2, and your text is in A2, then maybe something like this...
=IF(EXACT(UPPER(A1),A1),B1,"") The "" will return a "blank" cell
If your text is in A2 and you want to return it in B2 only if it meets the criteria,
In B2, try this....
=IF(EXACT(UPPER(A2),A2),A2,"")
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks