how to extract first two characters of second word from a column in excel?

Question

I wanted to extract the first two characters of the second word in a column on excel. That was my goal.

I have already found the solution and I would like to share with you the solution because I searched on the net and I had not found.

Here is the formula used:

=UPPER(LEFT((RIGHT(**G2**;LEN(**G2**)-FIND(" ";**G2**)));2)) 

where G2 is the column index on excel and UPPER to put everything in uppercase.

=UPPER( CONCATENATE( (LEFT(G3; 3)); (LEFT((RIGHT(G3;LEN(G3)-FIND(" ";G3)));2))) )

Column	first two characters (from second word)
Accipiter gentilis	GE
Actitis hypoleucos	HY

In addition, I also want to add that to extract the first two characters of each word (among the two) and concatenate them just apply the following formula:

=UPPER( CONCATENATE( (LEFT(G3; 2 )); (LEFT((RIGHT(G3;LEN(G3)-FIND(" ";G3)));2))) )

Result:

column	first tow caracters (from first and second word)
Accipiter gentilis	ACGE
Actitis hypoleucos	ACHY

If there are other supplements or other formulas that work the same way

Answer 1

To get first 2 letters from second word:

=INDEX(LEFT(TEXTSPLIT(UPPER(A1)," "),2),2)

To get first 2 letters from every word:

=CONCAT(LEFT(TEXTSPLIT(UPPER(A4)," "),2))

To get first 2 letters from first 2 words:

=CONCAT(INDEX((LEFT(TEXTSPLIT(UPPER(A7)," "),2)),SEQUENCE(2)))

Result:

Answer 2

From the explanations of your post it looks like you are working with the scientific names of Northern goshawk & Common sandpiper may be you have more and along with those names there could be some other features of the birds. Therefore for your first question, you could try using TEXTAFTER() and for the second one you can use TAKE() & TEXTSPLIT()

---

First Two Characters (From Second Word)

• Formula used in cell B2

=UPPER(LEFT(TEXTAFTER(A2," "),2))

Or,

• Formula used in cell C2

=UPPER(LEFT(TEXTAFTER(A2:A3," "),2))

---

First Two Characters From Every Word

• Formula used in cell B8

=CONCAT(UPPER(LEFT(TEXTSPLIT(A8," "),2)))

---

First Two Characters (From First And Second Word)

• Formula used in cell B14

=CONCAT(UPPER(LEFT(TAKE(TEXTSPLIT(A14,," "),2),2)))

---

Since I am not sure about your Excel Version, here are few alternative versions, you could try:

---

First Two Characters (From Second Word)

• Formula used in cell B2

=UPPER(LEFT(FILTERXML("<m><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></m>","//b[2]"),2))

---

Or,

• Formula used in cell C2

=UPPER(LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100)),2))

---

Or,

• Formula used in cell D2

=UPPER(LEFT(REPLACE(A2,1,FIND(" ",A2),""),2))

---

First Two Characters From Every Word

• Formula used in cell B10

=CONCAT(UPPER(LEFT(FILTERXML("<m><b>"&SUBSTITUTE(A10," ","</b><b>")&"</b></m>","//b"),2)))

---

Or,

• Formula used in cell C10

=CONCAT(UPPER(LEFT(TRIM(MID(SUBSTITUTE(A10," ",REPT(" ",100)),COLUMN($A$1:$Z$1)*100-99,100)),2)))

---

First Two Characters (From First And Second Word)

• Formula used in cell B17

=CONCAT(UPPER(LEFT(FILTERXML("<m><b>"&SUBSTITUTE(A17," ","</b><b>")&"</b></m>","//b[position()<3]"),2)))

---

Or,

• Formula used in cell C17

=CONCAT(UPPER(LEFT(TRIM(MID(SUBSTITUTE(A17," ",REPT(" ",100)),COLUMN($A$1:$B$1)*100-99,100)),2)))

---