Extract first 3 characters & first 4 characters from a alphanumeric value

[vikrampnz]

Hello

I have a list that looks like this in column B:

B-101
B-404
B-602
B-905
B-1003
B-1106 and so on.

I am trying to extract first 3 characters in case where the value has 5 characters and first 4 characters in cases where the value has 6 characters in another column.

For example:

Column B Column C
B-101 >>>>> B-1
B-404 >>>>> B-4
B-602 >>>>> B-6

B-1003 >>>>> B-10
B-1106 >>>>> B-11

I am using following formula:

=LEFT(B3,2*(LEFT((RIGHT(B3,LEN(B3)-SEARCH("-",B3))*1),LEN(RIGHT(B3,LEN(B3)-SEARCH("-",B3))*1)-2)))

I am getting "B-", but not the numbers after "-".

Can someone please help? I would appreciate any help.

Thanks

 

[YasserKhalil]

Try this formula

=LEFT(A1,2+IF(LEN(A1)=5,1,2))

 

[Fluff]

Two other options

Fluff.xlsm
	A	B	C	D
1
2		B-101	B-1	B-1
3		B-404	B-4	B-4
4		B-602	B-6	B-6
5		B-905	B-9	B-9
6		B-1003	B-10	B-10
Sheet4

Cell Formulas
Range	Formula
C2:C6	C2	=LEFT(B2,3+(LEN(B2)=6))
D2:D6	D2	=SUBSTITUTE(B2,RIGHT(B2,2),"")

 

[vikrampnz]

Thank you so much Fluff !! It did the trick !!

 

[vikrampnz]

Thanks Yasserkhalil for the prompt response.

 

[Phuoc]

Or try this

Book1
	A	B	C	D
1
2		B-101	B-1	B-1
3		B-4040	B-40	B-
4		B-602	B-6	B-6
5		B-905	B-9	B-9
6		B-1010	B-10	B-
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=REPLACE(B2,LEN(B2)-1,2,"")
D2:D6	D2	=SUBSTITUTE(B2,RIGHT(B2,2),"")

 

[Peter_SSs]

Thank you so much Fluff !! It did the trick !!

I assume that you mean the first formula?
The second formula could fail depending on the column B values - see post #6 column D which shows the same formula with some incorrect results.

 

[vikrampnz]

Hello Peter _SSs

I did not use the second formula. I used the first formula and it worked well.