PDA

View Full Version : VBA separate uppercase words from lowercase words.



mdalala
11-03-2017, 08:15 AM
Hi everyone. Basically, I have a txt file with different words and each one has it's own row. I have to copy them using VBA from txt to Excel file and then separate the UPPERCASE and LOWERCASE words. Words that start with the UPPERCASE should be in column A, but words that start with LOWERCASE should be in column B.
I have this code so far which copies the words from txt file to excel:


Public Sub aaa()


Dim i As Long, j As Long
Dim myStr As String
Dim Words() As String


'CHECK THE FILE EXISTS, IF NOT QUIT
If Dir("C:\Users\Robert\Desktop\ekselis\forexcel.txt") = "" Then
MsgBox ("File not Found")
Exit Function 'ADDED QUIT MESSAGE
End If


'FILE TO OPEN
Open "C:\Users\Robert\Desktop\ekselis\forexcel.txt" For Input As #1


'LOOP COUNTER
i = 0


'LOOP THROUGH FILE
Do Until EOF(1)


'READ A LINE
Line Input #1, myStr


'SEPERATE THE LINE BY WORDS
Words = Split(myStr, " ")


'LOOP THROUGH WORDS ARRAY
For j = LBound(Words) To UBound(Words)


'IF THE WORD IS NOT A ""
If Words(j) <> "" Then


'THEN PLACE THE WORD IN THE CELL
ActiveCell.Offset(i, j) = Words(j)


End If


Next


i = i + 1
Loop




Close #1


End Function

I`m not really sure how to organise the IF statements in a loop to complete my task... Thanks!

Kenneth Hobs
11-03-2017, 09:29 AM
cross-posted https://www.excelforum.com/excel-programming-vba-macros/1207281-vba-separate-uppercase-words-from-lowercase-words.html

Paul_Hossler
11-03-2017, 10:59 AM
Welcome

1. Please check the FAQs about multi-posting

2. Please use the [#] icon to add CODE tags and paste the macro between them

Not tested, but maybe something like this



Option Explicit

Public Sub aaa()
Dim iUC As Long, iLC As Long, i As Long
Dim myStr As String
Dim Words() As String

If Dir("C:\Users\Robert\Desktop\ekselis\forexcel.txt") = "" Then
MsgBox ("File not Found")
Exit Sub
End If

iUC = 1
iLC = 1

Open "C:\Users\Robert\Desktop\ekselis\forexcel.txt" For Input As #1

Do While Not EOF(1)
Line Input #1, myStr
If Len(Trim(myStr)) > 0 Then
Words = Split(myStr, " ")
For i = LBound(Words) To UBound(Words)
Select Case Left(Words(i), 1)
Case "A" To "Z"
ActiveSheet.Cells(iUC, 1).Value = Words(i)
iUC = iUC + 1
Case "a" To "z"
ActiveSheet.Cells(iLC, 2).Value = Words(i)
iLC = iLC + 1
End Select
Next
End If
Loop

Close #1
End Sub