Extract first three chatacters

SimonHughes

Well-known Member
Joined
Sep 16, 2009
Messages
507
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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),"")
 
Upvote 0
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.
 
Last edited:
Upvote 0
Many thanks to the three of you - much appreciated

The formulas from Aladin and Rick both work perfectly. Very happy!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,192
Messages
6,170,646
Members
452,344
Latest member
LarryRSch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top