Selecting certain characters within a cell

[TXkleen]

I have data in cells that vary between 13 and 14 characters.

AA1234567BBBB
or
AAA1234567BBBB

I need to be able to extract the 1234567 data from each cell, but am not sure how. Can I somehow use the LEFT function to grab the 7 characters the proceed the last 4?

Thanks!

 

[JackDanIce]

Welcome to the board.

Try:

=SUBSTITUTE(SUBSTITUTE(A1,"A",""),"B","")

 

[TXkleen]

In my examples, the "AA" and "AAA" were just placeholders. The first 2 or 3 characters or each cell are a brand code, and they can vary wildly. Thus, I don't think that your code will work?

 

[FormR]

Can I somehow use the LEFT function to grab the 7 characters the proceed the last 4?

Hi, you could try this option which adopts the logic quoted above.

Excel 2013/2016
	A	B
1	1234567WXY	1234567
2	1234567WXYZ	1234567
3	A1234567WXYZ	1234567
4	AB1234567WXYZ	1234567
5	ABC1234567WXYZ	1234567
6	ABCD1234567WXYZ	1234567
7	ABCDE1234567WXYZ	1234567
8	ABCDEF1234567WXYZ	1234567
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,MAX(1,LEN(A1)-10),7)

 

[TXkleen]

For further clarification, the brand code can be 2 or 3 digits, and vary wildly. The 7 character product code (shown as 1234567 above) is a combo of numbers and letters, and the final four characters are a unit of measure code which can vary and contain numbers and letters.

Brand Code + Product Code + UOM

As the UOM and Product Code's length are both fixed, I think it would be best to somehow select the 7 characters that precede the last 4 characters. Is that doable?

 

[TXkleen]

Hi, you could try this option which adopts the logic quoted above.

Excel 2013/2016

	A	B
1	1234567WXY	1234567
2	1234567WXYZ	1234567
3	A1234567WXYZ	1234567
4	AB1234567WXYZ	1234567
5	ABC1234567WXYZ	1234567
6	ABCD1234567WXYZ	1234567
7	ABCDE1234567WXYZ	1234567
8	ABCDEF1234567WXYZ	1234567

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =MID(A1,MAX(1,LEN(A1)-10),7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

We were typing at the same time. You nailed it!! Thank you so much!!