CODEWITHSUNDEEP
excelexcel-formulams-officeformula
user1866186
user1866186

Reputation: 1

creating abbreviations for a text with more than 2 spaces using excel formula

I am trying to write a formula to create an abbreviation of a text that contains more than two spaces in a excel cell using a formula that will trim the first word of every string after space and put it as a string like the example given below.

Example : Global Remote Access Management System - GRAMS

Formula :

=IF(OR(LEN(K7)>10,((LEN(K7)-LEN(SUBSTITUTE(K7," ","")))>2),CONCATENATE((LEFT(K7),3),RIGHT(K7,3)),K2))

I am not sure why it's not working. I tried to trouble individually and it works but as a formula, it fails.

Any help will be greatly appreciated. Thank you in advance.

Upvotes: 0

Views: 974

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

In later versions of Excel with the CONCAT function, you can use:

=CONCAT(LEFT(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")))

If you want to not abbreviate phrases with only a single space, you can test for that with an IF.

Algorithm

  • Create an XML out of the string with the nodes being each space separated substring
  • Concatenate the first letter of each node

The above formula will work with any number of words.

If you have an earlier version of Excel, without the necessary functions for the above formula, you can either use VBA, or you can use a variation of the following formula:

seq_99 is a Named Formula referring to: 

=IF(ROW($A$1:INDEX($A:$A,255,1))=1,1,1*(ROW($A$1:INDEX($A:$A,255,1))-1)*99)

and then, the main formula:

=INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),1,1) &
     INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),2,1) &
       INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),3,1) &
        INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),4,1) &
          INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),5,1) &
            INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),6,1) &
              INDEX(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),seq_99,99))),7,1)

Note that each line represents a letter, and you will need as many lines as there are possible words in the string. (You can have more lines, as those that don't match will merely add a null string to the string, which is nothing).

enter image description here

Upvotes: 0

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

If you have Excel 365 or Excel 2019 or Excel Web then following formula shall work for you.

=TEXTJOIN("",TRUE,MID(TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",99)),ROW($A$1:$A$10)*99,99)),1,1))

Notes:

  1. Above formula will work for 10 words. If there are more then change ROW($A$1:$A$10) part to suit. It assumes your data is in cell A1.
  2. This is an array formula and therefore needs to be committed by CTRL+SHIFT+ENTER simultaneously. If done correctly then Excel will wrap it with {} braces automatically.

Upvotes: 1

Related Questions