Concatenate only upper case letters

0 votes

In Excel (2021), I have a cell which contains words:

all Upper case LeTters supposed to be In result

And I need the result:

ULTI

Without VBA, how can I perform those tasks using only Excel functions?

After searching, I discovered that all "Upper case" and "concatenate" methods result in concatenate AND create an upper case, rather than concatenate IF uppercase.

Although I did find one, I believe (hope) there is a better solution because it is very ugly:

=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(txt,"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","")," ","")
Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
385 views

1 answer to this question.

0 votes

In ms365, try:

enter image description here

The formula in B1:

=CONCAT(TEXTSPLIT(A1,TEXTSPLIT(A1,CHAR(ROW(65:90)),,1)))

 If not a single character other than uppercase alpha is present, this would throw and error. In such case, simply add a trailing character other than uppercase alpha to the inner TEXTSPLIT(), e.g: =CONCAT(TEXTSPLIT(A1,TEXTSPLIT(A1&"a",CHAR(ROW(65:90)),,1)))


For Excel 2021:

=LET(x,MID(A1,SEQUENCE(LEN(A1)),1),CONCAT(IF((CODE(x)>64)*(CODE(x)<91),x,"")))
answered Feb 16, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Is JavaScript a case-sensitive language?

Yes, JavaScript is a case sensitive language.  The language ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
5,521 views
+1 vote
0 answers

where to put tf files while building servers from terraform.. its creating only workspace.

# kitchen create -----> Starting Kitchen (v2.2.5) -----> Creating ...READ MORE

Aug 4, 2019 in Others by Priyanka
505 views
+1 vote
0 answers

I am not able to see the chat box on webinar-session. i can see only ask question pane

Today (8/aug/2020) I have joined one webinar, ...READ MORE

Aug 9, 2020 in Others by Ravibharathi
• 130 points
1,213 views
0 votes
0 answers

MS EXCEL: Convert (Height) Decimal Feet & Inches to Inches Only

Using MS Excel (2010), I would like ...READ MORE

Feb 10, 2022 in Others by Edureka
• 13,670 points
448 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,547 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,688 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
832 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,032 views
0 votes
1 answer

Googlebot is accessing .aspx pages, it should access SEO-friendly URLs only

google bot will find  it if you ...READ MORE

answered Feb 11, 2022 in Others by narikkadan
• 63,720 points
395 views
0 votes
1 answer

How to disable a link using only CSS

[aria-current="page"] {   pointer-events: none;   cursor: default;   text-decoration: none;   color: black; } <a href="link.html" ...READ MORE

answered Feb 14, 2022 in Others by narikkadan
• 63,720 points
527 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP