Removing characters from sentence of varying length

[Jammyjt]

Hello,

I am trying to extract specific data from data descriptions with varying lengths. For example:

So in better words, I am trying to take the first letter of each piece of data, followed by the hyphen and then all the numbers. The examples have varying length before the hyphen as well as after. Additionally, some examples have a letter at the end of the numbers which needs to be removed.

I have been using the following formula to remove the letter at the end, if there is one:

=IF(ISNUMBER(--RIGHT(A1)),A1,LEFT(A1,LEN(A1)-1))

For the single letter before the hyphen I have tried using the LEFT function but I am uncertain how to mix the two together, maybe it requires a different solution altogether. Any help on this would be greatly appreciated!

Cheers,

Jamie

 

[JEC]

Try this

=LET(x,MID(A1,FIND("-",A1)+1,LEN(A1)),LEFT(A1,1)&"-"&MID(x,1,IF(ISNUMBER(--RIGHT(A1,1)),LEN(A1),LEN(x)-1)))

 

[FormR]

Hi, here's another possible option to try.

Book1
	A	B
1	abc-123456	a-123456
2	bd-234567	b-234567
3	cdg-1234567A	c-1234567
4	fg-123456B	f-123456
5	tdga-123456P	t-123456
6	xyzp-12345	x-12345
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=LEFT(A1)&MID(A1,FIND("-",A1),(LEN(A1)-FIND("-",A1)+1)-NOT(ISNUMBER(RIGHT(A1)+0)))

 

[Jammyjt]

Hey JEC, unfortunately I don't have the version of Excel that has the LET function, still waiting on the update to come but thank you for your prompt response.

FormR, thank you for your response, this is exactly what I'm looking for, works perfectly with the ability to remove the end of the formula to allow the letter to appear too.

Thank you both!

Cheers,

Jamie

 

[Peter_SSs]

A bit late but another one would be

21 09 30.xlsm
	A	B
1	abc-123456	a-123456
2	bd-234567	b-234567
3	cdg-1234567A	c-1234567
4	fg-123456B	f-123456
5	tdga-123456P	t-123456
6	xyzp-12345	x-12345
Extract

Cell Formulas
Range	Formula
B1:B6	B1	=REPLACE(LEFT(A1,LEN(A1)-ISERROR(RIGHT(A1,1)+0)),2,FIND("-",A1)-2,"")

 

[Jammyjt]

Hi Peter,

Thanks for sending this in, this also works great! Really appreciate the help from everyone.

Cheers,

Jamie