[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

change case in excel of alphanumeric text using a macro

I have a large excel file that has a large amount of text in a specific cell (description) for every row (record).

the text is all uppercase.  i can convert it to lower case in excel that is not the problem.

However, sometimes the cell contains information like

CARTRIDGE LEXMARK E198525BE 5000 PAGES.

I want the result to be :

Cartridge Lexmark E198525BE 5000 pages.

So starting with a capital for the string and all the rest lower case EXCEPT is there's a alphanumeric piece it should be in capitals (E198525BE in the example)

Can anyone give ma a macro of a formula for this ?
0
ggc2
Asked:
ggc2
  • 4
  • 3
1 Solution
 
Rory ArchibaldCommented:
Quick question - should the output have 'Pages' or 'pages' at the end? (and if the latter, why?)
0
 
Rory ArchibaldCommented:
Try this code - select your data before running it:

Sub ProperCaseData()
    Dim rngCell As Range
    Dim objRegExp As Object
    Dim match
    Dim n As Long
    Dim strTemp As String
    
    Application.ScreenUpdating = False
    Set objRegExp = CreateObject("vbscript.regexp")
    With objRegExp
        .IgnoreCase = True
        .Pattern = "(\b[A-Z]+\b)"
        .Global = True
        For Each rngCell In Selection
            strTemp = rngCell.Value
            Set match = .Execute(strTemp)
            If match.Count Then
                For n = 1 To match.Count
                    strTemp = Replace(strTemp, match(n - 1), StrConv(match(n - 1), vbProperCase))
                Next n
                rngCell.Value = strTemp
            End If
        Next rngCell
    End With
    Application.ScreenUpdating = True 
End Sub

Open in new window

0
 
bromy2004Commented:
How about this as a Formula

Function TextToLCase(strValue As String) As String
Dim Word() As String
Dim i As Long
Dim j As Long
Dim AlphaNum As Boolean 
Word = Split(strValue, " ") 
For i = 0 To UBound(Word)
  AlphaNum = True
  For j = 1 To Len(Word(i))
  
    If Not ((Asc(Mid(Word(i), j, 1)) >= 65 And Asc(Mid(Word(i), j, 1)) <= 90) Or (Asc(Mid(Word(i), j, 1)) >= 97 And Asc(Mid(Word(i), j, 1)) <= 122)) Then
    AlphaNum = False
    End If
  Next j
  If AlphaNum Then Word(i) = LCase(Word(i))
Next i 
TextToLCase = Join(Word, " ")
End Function

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ggc2Author Commented:
@Rorya : Thank you for your response.  The result you gave me works fine. But it generates a result like this :

One Capital For Each New Word But The E1285A Is OK.

So everything is ok, but i only require 1 capital in the beginning of the "Sentence".  The rest should be lowercase EXCEPT when it is an alphanumeric piece of string.

Could you please make for me the adjustment in the macro?  So in the question you ask first, it should be "pages" not "Pages"


Thank you very much in advance
0
 
Rory ArchibaldCommented:
That doesn't match the sample you posted though! :)
Try this:
 

Sub ProperCaseData() 
    Dim rngCell As Range 
    Dim objRegExp As Object 
    Dim match 
    Dim n As Long 
    Dim strTemp As String 
     
    Application.ScreenUpdating = False 
    Set objRegExp = CreateObject("vbscript.regexp") 
    With objRegExp 
        .IgnoreCase = True 
        .Pattern = "(\b[A-Z]+\b)" 
        .Global = True 
        For Each rngCell In Selection 
            strTemp = rngCell.Value 
            Set match = .Execute(strTemp) 
            If match.Count Then 
                For n = 1 To match.Count 
                    If n = 1 then 
                        strTemp = Replace(strTemp, match(n - 1), StrConv(match(n - 1), vbProperCase), 1, 1)
                    Else
                        strTemp = Replace(strTemp, match(n - 1), LCase$(match(n - 1), 1, 1)
                    End If
                Next n 
                rngCell.Value = strTemp 
            End If 
        Next rngCell 
    End With 
    Application.ScreenUpdating = True  
End Sub

Open in new window

0
 
ggc2Author Commented:
@rorya : thank you for the response, i know, i found it hard describing the problem and then tranlate it to english :-)

now i get an error when i run the macro :

the debugger says the error is in this line (line 22):

strTemp = Replace(strTemp, match(n - 1), LCase$(match(n - 1), 1, 1)

can you tell me what is wrong with it ?

thank you !
0
 
Rory ArchibaldCommented:
Sorry - that's the problem with air code! There's a parenthesis missing:
 
strTemp = Replace(strTemp, match(n - 1), LCase$(match(n - 1)), 1, 1)
 
0
 
ggc2Author Commented:
Fantastic work rorya!

Thank you for the persistance in solving my problem !

Happy Holidays
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now