Pulling a 10 digit character that begins with a B - Formula help!

Hello, friends. Today I am trying to write a formula that will pull any 10 character ASIN that begins with a "B". The Asin will always begin with a B and be 10 characters long. I am only wanting the formula to pull in the asin from the row it is entered into. Thank you in advance for any help you can provide!

Sample Data:


BLT70 KB520-7999 16.34 8000 2016-06-14 12:57:12 PDT y 1 This is a brand new item! 11 B01AGQ69GQ
0429PV9CUU1 BLT70 KB520-9998 20.84 8000 2015-04-29 12:39:41 PDT y 1 Heavy Duty 11
16.11 7999 2016-06-14 12:57:16 PDT y 1 This is a brand new item! 11 B01AIWA8CE
16.61 8000 2016-06-14 12:57:15 PDT y 1 This is a brand new item! 11 B01AGQ3Q6W
16.61 8000 2016-06-14 12:57:14 PDT y 1 This is a brand new item! 11 B01AGQ1RY0
16.88 8000 2016-06-14 12:57:17 PDT y 1 This is a brand new item! 11 B01AGQ5BLU
BLT70 KB580-50 19.71 7999 2014-01-27 08:09:47 PST y 1 11 B001OK8RPA
BLT70 KB580-7998 17.95 8000 2016-06-14 12:57:20 PDT y 1 This is a brand new item! 11 B01AGQ06OC
BLT70 KB580-7999 17.95 8000 2016-06-14 12:57:19 PDT y 1 This is a brand new item! 11 B01AGQ05F2
BLT70 KB590-27 19.71 7999 2014-01-27 13:41:22 PST y 1 11 B001OKD962
BLT70 KB590-7999 18.22 8000 2016-06-14 12:57:21 PDT y 1 This is a brand new item! 11 B01AGQAD6S
BLT70 KB600-7999 18.49 8000 2016-06-14 12:57:22 PDT y 1 This is a brand new item! 11 B01AGQ7BCM
BLT70 KB610-7998 16.34 8000 2016-06-14 12:57:24 PDT y 1 This is a brand new item! 11 B00NX4LV76
BLT70 KB610-7999 18.76 8000 2016-06-14 12:57:23 PDT y 1 This is a brand new item! 11 B01AGQ3GOE
BLT70 KB620-7997 19.03 7994 2016-06-14 12:57:26 PDT y 1 This is a brand new item! 11 B01AGQ04BC
BLT70 KB620-7998 19.03 8000 2016-06-14 12:57:25 PDT y 1 This is a brand new item! 11 B01AGPZUQ2
BLT70 KB620-7999 19.03 8000 2016-06-14 12:57:24 PDT y 1 This is a brand new item! 11 B01AGQ4044

Try this in B1:

=IF(LEFT(RIGHT(TRIM(A1),10))="B",RIGHT(TRIM(A1),10),"")

That doesnt seem to do it for me. The ASIN number is found in different columns within the row so I am trying to search the entire row for any 10 character number that begins with a B and pull that in.

Originally Posted by 63falcondude

Try this in B1:

=IF(LEFT(RIGHT(TRIM(A1),10))="B",RIGHT(TRIM(A1),10),"")

That doesnt seem to do it for me. The ASIN number is found in different columns within the row so I am trying to search the entire row for any 10 character number that begins with a B and pull that in.

From the looks of your "sample" from post #1, I assumed that everything was in one column.

I recommend uploading a small representative sample workbook of the data along with the desired result (which you can enter manually) based on that sample.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Originally Posted by 63falcondude

From the looks of your "sample" from post #1, I assumed that everything was in one column.

I recommend uploading a small representative sample workbook of the data along with the desired result (which you can enter manually) based on that sample.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Sorry, having issues uploading attachments

The ASIN number is found in different columns within the row so I am trying to search the entire row for any 10 character number that begins with a B and pull that in. Any help you can provide would be greatly appreciated

Sorry, having issues uploading attachments

Did you follow the instructions from post #5? If you tried using the paperclip instead, that could be what is giving you issues.

Originally Posted by 63falcondude

Did you follow the instructions from post #5? If you tried using the paperclip instead, that could be what is giving you issues.

I am dumb. You should see it now.

Try this in X1:

=INDEX(A1:T1,MATCH("B?????????",A1:T1&"",0)) Ctrl Shift Enter

Drag the formula down.

Originally Posted by 63falcondude

Try this in X1:

=INDEX(A1:T1,MATCH("B?????????",A1:T1&"",0)) Ctrl Shift Enter

Drag the formula down.

That does not seem to work. Does that one work for you?

Yes it does work for me.

What results are you getting to make you say that it doesn't work?

If you are getting #VALUE! errors, you didn't confirm the formula using Ctrl Shift Enter (the bold part in post #9).

Originally Posted by 63falcondude

Yes it does work for me.

What results are you getting to make you say that it doesn't work?

If you are getting #VALUE! errors, you didn't confirm the formula using Ctrl Shift Enter (the bold part in post #9).

63falcondude, you are the man. I didnt realize I had to double click in the cell before I hit that. I am a noob. Thank you again for your help! It is working great!

Happy to help. Thanks for the rep!

Originally Posted by 63falcondude

If you are getting #VALUE! errors, you didn't confirm the formula using Ctrl Shift Enter (the bold part in post #9).

Had a quick glance at this earlier, I noticed the dupe but you had already flagged it.

Curious as to why you've used CSE when using =INDEX(A3:T3,MATCH("B?????????",A3:T3,0)) without array confirmation should do as needed. Did I miss something?

If I'm not missing anything, given that the ASIN appears to be in the last populated column of each row, =LOOKUP("zzz",A3:T3) should also work, but only if that definitely a consistent factor.

Hi Jason. =LOOKUP("zzz",A3:T3) does work in the sample from post #8.

The sample from post #1 had one instance where the ASIN was not there so the LOOKUP formula would return an undesired result in that instance.

The formula from post #9 does have to be entered as an array formula. You missed the &"" part.

Ooh I see what you're saying. Yes I think you're right that you can do without the &"" section and thus enter the formula normally.

It was the need for the &"" with the array that was making me think i had missed something (it was omitted from my post )

=INDEX(A3:T3,MATCH("B?????????",A3:T3,0))

and

{=INDEX(A3:T3,MATCH("B?????????",A3:T3&"",0))}

Both appear to work correctly.

I wasn't trying to say it was wrong, just wondering what your logic was behind the suggestion. Quite often simple things are easily missed, I thought maybe you were seeing something that I wasn't.