Return first 3 characters after nth comma

[danhenshy23]

Hi,

I have a text string like the one below:

France, Germany, Mexico, Australia, Croatia

I am wanting to return the first 3 characters after the nth comma, i.e. Mex or Aus.

How can I go about this?

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G
France, Germany, Mexico, Australia, Croatia
n
Fra	Ger	Mex	Aus	Cro

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IFERROR(MID(", "&$A$1,FIND("|",SUBSTITUTE(", "&$A$1,",","|",B3+1))+2,3),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Assuming your text is less than 200 characters long, here is another formula for you to consider (replace N with the comma number, or a cell reference with the comma number in it, that you want to find the text after... use 0 for the beginning of the text)...

=LEFT(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",200)),(1+N)*200,200)),3)

 

[danhenshy23]

Perfect! Thanks

 

[jtakw]

Hi,

Another way using Eric's setup:

Book1
	A	B	C	D	E	F	G
1	France, Germany, Mexico, Australia, Croatia
2
3	n	0	1	2	3	4	5
4		Fra	Ger	Mex	Aus	Cro
Sheet52

Cell Formulas
Range	Formula
B4	=IFERROR(MID($A1,IF(B3=0,1,FIND("^",SUBSTITUTE($A1,", ","^",B3))+2),3),"")

 

[Eric W]

Glad we could help