Extract first three chatacters

[SimonHughes]

Hello, I need to extract the first three characters from a string where the characters are one of the following: UV2, UV3, UV4, UV5. The characters are always to the left of the column. I could probably use a LEFT, IF/OR formula but would appreciate any help.

I am using Excel 2010 on Win 7

 

[Matt Rogers]

Hi.

Something like this?

Excel Workbook
	A	B
1	UV10000	UV1
2	UV2ABCD	UV2
3	UV3AB33	UV3
Sheet

 

[SimonHughes]

My apologies, I simplified the task by not stating that I have a wide variety of part numbers starting with characters other than UV2 etc. Could be IF2 or PM6 etc and I just want to extract the UV2 and so on.

 

[Aladin Akyurek]

My apologies, I simplified the task by not stating that I have a wide variety of part numbers starting with characters other than UV2 etc. Could be IF2 or PM6 etc and I just want to extract the UV2 and so on.

Try something like...

=IF(OR(LEFT(A2,3)={"UV1","UV2","UV3","UV4","UV5"}),LEFT(A2,3),"")

 

[Rick Rothstein]

I'm a little unclear as to what you are actually after. Does this give you what you want?

=IF(LEFT(A1,2)="UV",IF(OR(--MID(A1,3,1)={2,3,4,5}),LEFT(A1,3),""),"")

EDIT NOTE: Use Aladin's formula... it will be more efficient than the one I posted.

 

[SimonHughes]

Many thanks to the three of you - much appreciated

The formulas from Aladin and Rick both work perfectly. Very happy!

 

[Matt Rogers]

Excel Workbook
	A	B
1	UV10000	UV1
2	ABCDEF
3	UV3AB33	UV3
Sheet

 

[Matt Rogers]

Many thanks to the three of you - much appreciated

The formulas from Aladin and Rick both work perfectly. Very happy!

Sorry, have not seen it.

 

[SimonHughes]

Hi Screwdriver, this works as well - but I cannot understand the logic. I see that COUNTIF(E125,"*UV*" does = 1 and therefore it returns what we want but how does COUNTIF(E125,"*UV*" = 1?

 

[Matt Rogers]

Hi.

It's another method.
See the red "2"

Excel Workbook
	A	B
1	UV10000
2	ABCDEF	ABC
3	UV3AB33
Sheet