How do I separate specific cell text

[zubair99]

Dear Excel Experts,

I like your work here on mrExcel platform.
I need to solve a problem. here it is.

How do I separate specific cell text automtically?
I have about 1000 rows like these.
I need only numbers which has at the start of every cell. mark red.
Please check and replay asap.
thanks in advance.

56789101112Sizing infoPlease select a Ring Size Main Stone Color:Please select a Main Stone Color Shipping:US $2.15to Pakistan via China Post Registered Air MailEstimated Delivery Time:24-40daysPlease select the country you want to ship fromServices:Quantity: piece (5467 pieces available) Purchases are limited to 5467piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 56789101112 <tbody> </tbody> 106789Sizing infoPlease select a Ring Size Metal Color:Black Gun PlatedPlease select a Metal Color Shipping:Free Shippingto Pakistan via SunYou Economic Air MailEstimated Delivery Time:15-60daysPlease select the country you want to ship fromServices:Quantity: piece (4940 pieces available) Purchases are limited to 4940piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 106789 6789Sizing infoPlease select a Ring Size Shipping:Free Shippingto Pakistan via China Post Registered Air MailEstimated Delivery Time:24-40daysPlease select the country you want to ship fromServices:Quantity: piece (3847 pieces available) Purchases are limited to 3847piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 78Please select a Ring Size Shipping:Free Shippingto Pakistan via China Post Ordinary Small Packet PlusEstimated Delivery Time:24-44daysPlease select the country you want to ship fromServices:Quantity: piece (1893 pieces available) Purchases are limited to 1893piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 6.57.58.59.5Sizing infoPlease select a Ring Size Shipping:Free Shippingto Pakistan via China Post Registered Air MailEstimated Delivery Time:24-40daysPlease select the country you want to ship fromServices:Quantity: piece (2121 pieces available) Purchases are limited to 2121piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 789Sizing infoPlease select a Ring Size Shipping:Free Shippingto Pakistan via China Post Ordinary Small Packet PlusEstimated Delivery Time:24-44daysPlease select the country you want to ship fromServices:Quantity: piece (2898 pieces available) Purchases are limited to 2898piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 1056789Sizing infoPlease select a Ring Size Shipping:Free Shippingto Pakistan via 4PX Singapore Post OM ProEstimated Delivery Time:33-51daysPlease select the country you want to ship fromServices:Quantity: piece (14437 pieces available) Purchases are limited to 14437piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 66.577.5895.5Please select a Ring Size Metal Color:Platinum PlatedRose Gold PlatedPlease select a Metal Color Shipping:Free Shippingto Pakistan via China Post Registered Air MailEstimated Delivery Time:24-40daysPlease select the country you want to ship fromServices:Quantity: piece (13975 pieces available) Purchases are limited to 13975piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 66.577.5895.5Please select a Ring Size Metal Color:Platinum PlatedRose Gold PlatedPlease select a Metal Color Shipping:Free Shippingto Pakistan via China Post Registered Air MailEstimated Delivery Time:24-40daysPlease select the country you want to ship fromServices:Quantity: piece (13975 pieces available) Purchases are limited to 13975piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> 5.56.589Sizing infoPlease select a Ring Size Metal Color:Rose Gold PlatedPlatinum PlatedPlease select a Metal Color Shipping:Free Shippingto Pakistan via AliExpress Standard ShippingEstimated Delivery Time:21-38daysPlease select the country you want to ship fromServices:Quantity: piece (595 pieces available) Purchases are limited to 595piecesTotal Price:Depends on the product properties you select(Approximately ) <tbody> </tbody> <tbody> </tbody>

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

 

[AlanY]

are the text following the numbers is always just "Sizing" and "Please"?

 

[zubair99]

not every time same text with the numbers.

 

[zubair99]

you can generate a formula with most common text "Sizing" "Please" I will change if the text will be changed.

 

[AlanY]

try this in B1 and copy down

=LEFT(A1,MIN(IFERROR(FIND("S",A1),999),IFERROR(FIND("P",A1),999))-1)