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
1234567WXY
1234567WXYZ
A1234567WXYZ
AB1234567WXYZ
ABC1234567WXYZ
ABCD1234567WXYZ
ABCDE1234567WXYZ
ABCDEF1234567WXYZ

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1234567[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1234567[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1234567[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1234567[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1234567[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1234567[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1234567[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1234567[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MID(A1,MAX(1,LEN(A1)-10),7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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