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


[TABLE="width: 155"]
<tbody>[TR]
[TD][TABLE="width: 277"]
<tbody>[TR]
[TD]001-01-Australia-02-06[/TD]
[/TR]
[TR]
[TD]12-2-Japan-65[/TD]
[/TR]
[TR]
[TD]236-01-India-45-01[/TD]
[/TR]
[TR]
[TD]1401-3-Singapore-458-1[/TD]
[/TR]
[TR]
[TD]456-01-France-456-2[/TD]
[/TR]
[TR]
[TD]12-9-China-45-69[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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...


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

001-01-Australia-02-06​

[/TD]
[TD]

AUS​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12-2-Japan-65​

[/TD]
[TD]

***​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

236-01-India-45-01​

[/TD]
[TD]

IND​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1401-3-Singapore-458-1​

[/TD]
[TD]

SIN​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

456-01-France-456-2​

[/TD]
[TD]

FRA​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

12-9-China-45-69​

[/TD]
[TD]

CHI​

[/TD]
[/TR]
</tbody>[/TABLE]


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...


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

001-01-Australia-02-06​

[/TD]
[TD]

AUS​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12-2-Japan-65​

[/TD]
[TD]

***​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

236-01-India-45-01​

[/TD]
[TD]

IND​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1401-3-Singapore-458-1​

[/TD]
[TD]

SIN​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

456-01-France-456-2​

[/TD]
[TD]

FRA​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

12-9-China-45-69​

[/TD]
[TD]

CHI​

[/TD]
[/TR]
</tbody>[/TABLE]


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.