Extracting Inventory number from a string

[roxdrob]

Could anyone tell me how to make a formula that will pull out my inventory number from a string? The inventory numbers can be anywhere from 3 characters to 8. Please note that only in this example are the preceding "G" is present. Sometimes the inventory number is at the end of the string. TIA!

G BM1240 Bookmark $9.00
G MB16 Men's Bracelet $23
G CC1225 Car Charm $12
G FRB1323 Full Rosary Bracelet $35
G E1330 Earrings $8
G B2174 Bracelet $13
G AR9 Anglican Rosary $17
G SC2030 Stethoscope Charm $10
G R1452 Rosary $31
G ODR1428 One Decade Rosary $11
G CC1248 Car Charm $13.00
G PR1444 Pocket Rosary $11
G RB1545 Rosary Bracelet $14

 

[ZVI]

Something like this?
=IF(FIND(" ",A1)=2,MID(A1,3,FIND(" ",A1,3)-2),MID(A1,1,FIND(" ",A1,3)-1))
Please post other examples of the the inventory number if needed

 

[roxdrob]

Thank you. That works with that instance, but is there a universal formula that I could use with that example as well as this example? Is this an impossible or unreasonable request?


[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD]"This unique double bracelet is handmade with blue glass beads and is accented with silver. It is closed with a toggle clasp and measures approximately 8 inches in length.

Size can be adjusted if needed - please put desired size in comments when checking out. Custom orders are always welcome! All pieces are handmade by me and unique - no two pieces are alike. All pieces are made and ready to ship. If you would like it prepared as a gift (in an organza bag), please let me know when checking out. I will do this at no additional charge.

To view more bracelets from my shop click the link below,
https://www.mrexcel.com/forum/excel...ting-inventory-number-string.html#post4949107

To visit my shop and view all my handmade fashion jewelleryclick the link below.
https://www.mrexcel.com/forum/excel-questions/1031107-extracting-inventory-number-string.html
B655"
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ZVI]

The below formula works for two possible Inventory number placements:
1. Inventory number is placed after the price with "$" symbol
2. Inventory number starts from the 3nd character

=IF(ISNUMBER(FIND(" ",A1,FIND("$",A1))),MID(A1,FIND(" ",A1,FIND("$",A1))+1,99),MID(A1,3,FIND(" ",A1,3)-2))

P.S. The promoting links are forbidden here, please delete it

 

[roxdrob]

When I use that, I only get letters, like "is", "d". Can I attach a copy of what I'm working with via spreadsheet? I don't see where to do that, but I did add a few cells for example. TIA!
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]This beautiful Catholic Full Rosary Chaplet Bracelet is handmade with turquoise howlite beads. The cross and medallion are antique silver-finished pewter. Gift them for your Baptisms, First Holy Communion, Confirmations, Wedding party, or just a lovely gift for loved ones. Chaplet is approximately 7 inches in length.

To view more rosary bracelets in my shop, please click here: https://www.mrexcel.com/forum/excel...ting-inventory-number-string.html#post4949107

To view all of the jewelry in my shop, please click here: https://www.mrexcel.com/forum/excel-questions/1031107-extracting-inventory-number-string.html

FRB2211[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Add some bling and show your individuality with this stethoscope jewelry ID charm accessory.

This one of a kind stethoscope jewelry ID charm accessory features peach and white acrylic beads and shell shaped end caps.

Rubber rings on each end fit over ear tips to allow you to place the charm snugly on the tubing. It measures approximately 4 inches in length. Size can be adjusted if needed - please put desired size in comments when checking out. Custom orders are welcome! All pieces are handmade by me and unique - no two pieces are alike. All pieces are made and ready to ship.

To view more stethoscope jewelry accessories from my shop click the link below,
https://www.mrexcel.com/forum/excel...ting-inventory-number-string.html#post4949107

To visit my shop and view all my handmade fashion jewelry click the link below.
https://www.mrexcel.com/forum/excel-questions/1031107-extracting-inventory-number-string.html

SC296[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]This beautiful Catholic Rosary is handmade with brown glass beads and silver 3mm beads. The cross and medallion are antique silver-finished pewter. The Our Father beads are antique silver diamond shaped beads.

Gift them for your Baptisms, First Holy Communion, Confirmations, Wedding party, or just a lovely gift for loved ones.

I am happy to create a custom design just for you...please message me with your specifications. Custom orders are always welcome!

Dimensions:
Main necklace: 24 inches
Drop and crucifix: 5-6 inches

R042[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Add some bling and show your individuality with this stethoscope jewelry ID charm accessory.

This one of a kind stethoscope jewelry ID charm accessory features blue glass beads and are finished with shell shaped end caps.

Rubber rings on each end fit over ear tips to allow you to place the charm snugly on the tubing. It measures approximately 4 inches in length. Size can be adjusted if needed - please put desired size in comments when checking out. Custom orders are welcome! All pieces are handmade by me and unique - no two pieces are alike. All pieces are made and ready to ship.

To view more stethoscope jewelry accessories from my shop click the link below,
https://www.mrexcel.com/forum/excel...ting-inventory-number-string.html#post4949107

To visit my shop and view all my handmade fashion jewelry click the link below.
https://www.mrexcel.com/forum/excel-questions/1031107-extracting-inventory-number-string.html

SC275[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]This one of a kind clip on bookmark is adorned with a pink rhinestone heart suspended from black pearls and fuchsia glass beads and accented with silver.

Custom orders are welcome! All pieces are handmade by me and one of a kind - no two pieces are alike. They are made and ready to ship. If you would like it prepared as a gift (in an organza bag), please let me know when checking out. I will do this at no additional charge. Shipping is free in the United States and only $8.00 outside of the U.S.

To view more bookmarks from my shop click the link below,
https://www.mrexcel.com/forum/excel...ting-inventory-number-string.html#post4949107

To visit my shop and view all my handmade fashion jewelry click the link below.
https://www.mrexcel.com/forum/excel-questions/1031107-extracting-inventory-number-string.html

BM152[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Who dat? YOU dat with this beautiful bracelet. Old gold, black, and silver colored beads show your team devotion. This piece is handmade in Louisiana and is the perfect gift for the faithful fan in your life (even if that happens to be you!). I’m so happy to be spreading Saints' pride locally and worldwide.

This bracelet measures approximately 7.5 inches long and also closes with a toggle clasp. There is never any shipping cost within the United States. International shipping is just $8.00 USD.

Is this piece not quite right? I do custom orders! Click the “Request Custom Order� button in the sidebar.

B303[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Macropod]

roxdrob: Please don't include what might rightly be regarded as 'advertising' links in your posts. In this case, I've replaced them with links to this thread.

 

[roxdrob]

roxdrob: Please don't include what might rightly be regarded as 'advertising' links in your posts. In this case, I've replaced them with links to this thread.

I had removed the store name. Sorry if that wasn't enough.