CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
I Have Question
I Have Question

Reputation: 41

How to Get all uppercase characters from cell in excel?

I want all capital letters .. JUST LIKE, Casey Richard Omar W= CROW ,...
Processing of Dimensional Stone = PDS

like all capital letters or shortname

enter image description here

Upvotes: 2

Views: 5841

Answers (6)

tslupphaug
tslupphaug

Reputation: 17

If you want to avoid using VBA, @Harun24HR's solution is a messy, but very effective one. Here's a version with extra special characters for those in need:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Navn],"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z",""),"æ",""),"ø",""),"å",""),"á",""),"ç",""),"é",""),"í",""),"ó",""),"ú",""),"-","")," ","")

With ; instead of , (for Excel installations in other languages):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Navn];"a";"");"b";"");"c";"");"d";"");"e";"");"f";"");"g";"");"h";"");"i";"");"j";"");"k";"");"l";"");"m";"");"n";"");"o";"");"p";"");"q";"");"r";"");"s";"");"t";"");"u";"");"v";"");"w";"");"x";"");"y";"");"z";"");"æ";"");"ø";"");"å";"");"á";"");"ç";"");"é";"");"í";"");"ó";"");"ú";"");"-";"");" ";"")

Upvotes: 1

EEM
EEM

Reputation: 6659

It seems that this question is more about how to create a short name, with the initials of the names.

If that is the case then try this formula: 

    = CONCATENATE( LEFT( $A1 ),
    IFERROR( MID( $A1, 1 + SEARCH( "‡", SUBSTITUTE( $A1, " ", "‡", 1 ) ), 1 ), "" ),
    IFERROR( MID( $A1, 1 + SEARCH( "‡", SUBSTITUTE( $A1, " ", "‡", 2 ) ), 1 ), "" ),
    IFERROR( MID( $A1, 1 + SEARCH( "‡", SUBSTITUTE( $A1, " ", "‡", 3 ) ), 1 ), "" ),
    IFERROR( MID( $A1, 1 + SEARCH( "‡", SUBSTITUTE( $A1, " ", "‡", 4 ) ), 1 ), "" ) )

If the name has more than four words then you can just add more lines, like: 

    IFERROR( MID( $A1, 1 + SEARCH( "‡", SUBSTITUTE( $A1, " ", "‡", # ) ), 1 ), "" ),

were # represents the word number

If any of the words contain the character ‡, just changed it to any other uncommon character.

Upvotes: 1

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

If you have Excel 2016, which contains the useful function CONCAT, then you can use this array formula: (line breaks added for readability)

= CONCAT(MID(A1,MODE.MULT(IF(ABS(CODE(MID(A1,
  COLUMN(INDEX(1:1,1):INDEX(1:1,LEN(A1))),1))-77.5)<13,
  COLUMN(INDEX(1:1,1):INDEX(1:1,LEN(A1)))*{1;1})),1))

Otherwise, as already provided, VBA or formula with a bunch of SUBSTITUTEs are required.

Upvotes: 1

Tom
Tom

Reputation: 9878

You could use Regular Expressions to do this

Sub ExampleRegExp()
    Dim RegExp As Object
    Dim str As String

    str = "Casey Richard Omar W"

    Set RegExp = CreateObject("vbscript.regexp")
    With RegExp
        .Global = False
        .IgnoreCase = False
        .Pattern = "([a-z]+)|(\s)"

        Do While .test(str) = True
            str = .Replace(str, vbNullString)
        Loop

        MsgBox str
    End With
End Sub

Or using it as a UDF

Function GetUpperCase(str As String) As String
    Dim RegExp As Object

    Set RegExp = CreateObject("vbscript.regexp")
    With RegExp
        .Global = False
        .IgnoreCase = False
        .Pattern = "([a-z]+)|(\s)"

        Do While .test(str) = True
            str = .Replace(str, vbNullString)
        Loop

        GetUpperCase = str
    End With
End Function

in A2 I've entered =GetUpperCase(A1)

Output

Upvotes: 3

Harun24hr
Harun24hr

Reputation: 36870

Use following formula---

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z","")," ","")

enter image description here

Upvotes: 0

Jeremy
Jeremy

Reputation: 1337

Sounds like you're looking for the =UPPER() formula

Upvotes: -3

Related Questions