Very tricky question

[mark_ink]

Here I'd like to find the way to get starting 3 letters of every country.
paste these values into Column A

ie.
AUS for Australia
IND for India

Many thanks

001-01-Australia-02-06 12-2-Japan-65 236-01-India-45-01 1401-3-Singapore-458-1 456-01-France-456-2 12-9-China-45-69 <tbody> </tbody>

<tbody>
</tbody>

 

[Special-K99]

Well, that was easy, 2-3 mins though I've done something like this before

=UPPER(LEFT(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",500)),1000,500)),3))

 

[FormR]

Here's another possible option:

Excel 2013
	A	B
1	AUS	001-01-Australia-02-06
2	***	12-2-Japan-65
3	IND	236-01-India-45-01
4	SIN	1401-3-Singapore-458-1
5	FRA	456-01-France-456-2
6	CHI	12-9-China-45-69
Sheet1

Cell Formulas
Range	Formula
A1	=UPPER(MID(B1,FIND("-",SUBSTITUTE(B1,"-","#",1))+1,3))

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe...

	A​	B​
1​	001-01-Australia-02-06​	AUS​
2​	12-2-Japan-65​	***​
3​	236-01-India-45-01​	IND​
4​	1401-3-Singapore-458-1​	SIN​
5​	456-01-France-456-2​	FRA​
6​	12-9-China-45-69​	CHI​

<tbody>
</tbody>

Formula in B1 copied down
=UPPER(MID(A1,SEARCH("|",SUBSTITUTE(A1,"-","|",2))+1,3))

Hope this helps

M.

 

[mark_ink]

Thanks

Welcome to Mr Excel

Maybe...

	A​	B​
1​	001-01-Australia-02-06​	AUS​
2​	12-2-Japan-65​	***​
3​	236-01-India-45-01​	IND​
4​	1401-3-Singapore-458-1​	SIN​
5​	456-01-France-456-2​	FRA​
6​	12-9-China-45-69​	CHI​

<tbody>
</tbody>

Formula in B1 copied down
=UPPER(MID(A1,SEARCH("|",SUBSTITUTE(A1,"-","|",2))+1,3))

Hope this helps

M.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.