शेष शब्द को बदले बिना प्रत्येक शब्द के पहले अक्षर को पूंजी बनाने के लिए एक्सेल फॉर्मूला

Question

मैं यह सुनिश्चित करके कार्यपुस्तिका में पता प्रविष्टियों को सही करना चाहता हूं कि सभी शब्द बड़े अक्षरों का उपयोग करते हैं, हालांकि मैं PROPER का उपयोग नहीं कर सकता क्योंकि यह शेष शर्तों को भी छोटा कर देगा।

उदाहरण के लिए, अगर कोई 221B बेकर सेंट, स्प्रिंग हिल QLD, ऑस्ट्रेलिया में प्रवेश करता है, तो मैं इसे 221 बेकर सेंट, स्प्रिंग हिल QLD, ऑस्ट्रेलिया में बदलना चाहूंगा।

अगर मैं इसके लिए PROPER फ़ंक्शन का उपयोग करता हूं, तो QLD को Qld में बदल दिया जाता है, जो गलत है।

अब तक मैंने यह जांचने के लिए एक सूत्र लिखा है कि क्या कोई सेल पहले से ही अपरकेस में है, और यदि है, तो उस सेल मान को वैसे ही वापस कर दें। हालाँकि, यदि सेल अपरकेस में नहीं है, तो उस पर PROPER फ़ंक्शन का उपयोग करें।

=IF(EXACT(UPPER(A1),A1),A1,PROPER(A1))

मैं इस सूत्र का उपयोग करना चाहूंगा, हालांकि इसे पूरे सेल के बजाय प्रत्येक शब्द पर व्यक्तिगत रूप से लागू करें। मैं इसके लिए वीबीए का उपयोग करने में सक्षम नहीं हूं।

अद्यतन: मैंने इसे प्रबंधित किया है, हालांकि सूत्र बहुत ही सुरुचिपूर्ण नहीं है:

=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(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," a"," A")," b"," B")," c"," C")," d"," D")," e"," E")," f"," F")," g"," G")," h"," H")," j"," J")," k"," K")," l"," L")," m"," M")," n"," N")," o"," O")," p"," P")," q"," Q")," r"," R")," s"," S")," t"," T")," u"," U")," v"," V")," w"," W")," x"," X")," y"," Y")," z"," Z"),", a",", A"),", b",", B"),", C",", C"),", d",", D"),", e",", E"),", f",", F"),", g",", G"),", h",", H"),", j",", J"),", k",", K"),", l",", L"),", m",", M"),", n",", N"),", o",", O"),", p",", P"),", q",", Q"),", r",", R"),", s",", S"),", t",", T"),", u",", U"),", v",", V"),", w",", W"),", x",", X"),", y",", Y"),", z",", Z")

excel excel-formula

0

david_10001 15 मार्च 2020, 08:23

क्या पूरा पता एक सेल में है या आप इसे जोड़ रहे हैं?

–

ACCtionMan

15 मार्च 2020, 08:58

सभी एक सेल में

–

david_10001

15 मार्च 2020, 09:08

क्या कोई कारण है कि पता विभाजित नहीं किया जा सकता है? मैंने पिछले कुछ वर्षों में पते वाली कई स्प्रैडशीट्स के साथ काम किया है और यदि आप कभी भी उपनगर या राज्य द्वारा सॉर्ट या फ़िल्टर करना चाहते हैं, तो आपको उस सेटअप के साथ एक चुनौती होगी।

–

ACCtionMan

15 मार्च 2020, 09:20

आपको ", @" को स्थानापन्न करने की आवश्यकता नहीं है, वे पहले से ही " @" द्वारा बदल दिए गए हैं। तो आप उस फॉर्मूले को आधा कर सकते हैं

–

chris neilsen

15 मार्च 2020, 09:29

एफवाईआई यदि वह समाधान है जिस पर आपने समझौता किया है, तो आपको इसे अपने क्यू में संपादित करने के बजाय इसे उत्तर के रूप में पोस्ट करना चाहिए

–

chris neilsen

15 मार्च 2020, 09:31

Answer 1

वीबीए के बिना यह थोड़ा मुश्किल है।

आपको मूल रूप से चाहिए

स्ट्रिंग को शब्दों में विभाजित करें
प्रत्येक शब्द के पहले अक्षर को उसके अपर केस संस्करण से बदलें
वाक्य को फिर से जोड़ना

चरण 1 को FilterXML के साथ प्राप्त किया जा सकता है, अंतरिक्ष को सीमांकक के रूप में उपयोग करके (ध्यान दें, यह मानता है कि प्रत्येक शब्द कम से कम एक स्थान से अलग है, और संभवतः अन्य वर्ण भी)

=FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a")

चरण 2 प्रत्येक शब्द के पहले वर्ण को संसाधित करने के लिए Replace, Upper और Left का उपयोग करता है

=REPLACE(B1#,1,1,UPPER(LEFT(B1#,1)))

चरण 3 वाक्य को फिर से इकट्ठा करने के लिए TEXTJOIN का उपयोग करता है

=TEXTJOIN(" ",TRUE,C1#)

enter image description here

इन सबको एक साथ रखना एक ही सूत्र है

=TEXTJOIN(" ", TRUE,REPLACE(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1,1,UPPER(LEFT(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1))))

TEXTJOIN संस्करण 2017 में जोड़ा गया था। FILETERXML संस्करण 2013 में

इसे संस्करण>= 2013 और <2017 में काम करने के लिए, TEXTJOIN के बजाय CONCAT का उपयोग करें, साथ ही इसे काम करने के लिए कुछ ट्वीक का उपयोग करें।

=TRIM(CONCAT(REPLACE(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1,1," "&UPPER(LEFT(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1)))))

2

chris neilsen 16 मार्च 2020, 11:48

ऐसा लगता है कि मेरे पास मेरे संस्करण (कार्यालय 2016) में टेक्स्टजॉइन फ़ंक्शन नहीं है। क्या मैं ऐसा कुछ बना सकता हूं?

–

david_10001

15 मार्च 2020, 09:02

इसे एक्सेल के बाद के संस्करण में जोड़ा गया था। कुछ अतिरिक्त काम के साथ CONCAT का उपयोग करना एक विकल्प है

–

chris neilsen

15 मार्च 2020, 09:09

क्षमा करें, ऐसा भी लगता है कि मेरे पास FILTERXML विकल्प भी नहीं है।

–

david_10001

15 मार्च 2020, 09:12

ध्यान दें कि चरण दर चरण उदाहरण एक्सेल 365 में डायनामिक एरेज़ फीचर एड्रे का उपयोग करता है, इसलिए आपके लिए काम नहीं करेगा। संयुक्त संस्करण चाहिए (मुझे लगता है) लेकिन मैं पुराने संस्करण पर पाठ नहीं कर सकता

–

chris neilsen

15 मार्च 2020, 09:12

Google के अनुसार, FILTERXML को V2013 में जोड़ा गया था। अपग्रेड का समय हो सकता है।

–

chris neilsen

15 मार्च 2020, 09:12

Answer 2

इस लंबे फॉर्मूले को आजमाएं, जिसमें टेक्स्टजॉइन या CONCAT फ़ंक्शन की आवश्यकता नहीं है,

B1 में, सूत्र दर्ज करें:

=REPLACE(A1,1,MATCH(2,INDEX(1/(CODE(MID(LEFT(A1,LOOKUP(99,FIND(" ",A1,ROW($1:$99)))),ROW($1:$99),1))>96),0)),PROPER(LEFT(A1,MATCH(2,INDEX(1/(CODE(MID(LEFT(A1,LOOKUP(99,FIND(" ",A1,ROW($1:$99)))),ROW($1:$99),1))>96),0)))))

enter image description here

1

bosco_yip 15 मार्च 2020, 10:34

ध्यान दें कि यह काम नहीं करेगा यदि मूल 221B baker St, spring hill QLD, australia था और australia को वैसे ही छोड़ देगा।

–

Jerry

15 मार्च 2020, 10:49

Answer 3

मैंने अल्पविराम का उपयोग तत्व विभाजक के रूप में करते हुए, आपके पते को इसके घटकों में विभाजित किया है। हो सकता है कि यह आपके द्वारा प्राप्त सभी प्रकार के पतों पर काम न करे, लेकिन नीचे दिए गए सूत्र एक बेहतर समाधान के लिए आधार के रूप में काम कर सकते हैं जिस पर अभी काम किया जाना है।

A2 = 221B बेकर सेंट, स्प्रिंग हिल QLD, ऑस्ट्रेलिया
B2 =IFERROR(TRIM(बाएं(A2,FIND(",,",A2)-1)), A2)
C2 =MID(LEFT(A2,FIND("^",SUBSTITUTE(A2,",","^",2))-1), FIND(",",A2)+1,100)
डी 2 = ट्रिम (बाएं (सी 2, एलईएन (सी 2) - एलईएन (ई 2)))
E2 =TRIM(MID(C2,FIND("^",SUBSTITUTE(C2," ","^",LEN(C2)-LEN(SUBSTITUTE(C2, " ", "")))+1,100))
F2 =TRIM(MID(A2,FIND("^",SUBSTITUTE(A2,",","^",2))+1,100))

=PROPER(B2) & "," & PROPER(D2) & "" & UPPER(E2) & "," & PROPER(F2) वांछित परिणाम देता है।

-1

Variatus 15 मार्च 2020, 09:12

शेष शब्द को बदले बिना प्रत्येक शब्द के पहले अक्षर को पूंजी बनाने के लिए एक्सेल फॉर्मूला

3 जवाब

संबंधित सवाल

नए सवाल

excel