Searching for text and returning text

[GreggyR]

Hello

I have a list of products with their sizes all in one cell (for exampl
"Marlboro 25s"), I would like to search that specific cell for a piec
of text relating to the size (for example "25s") and return just that i
a separate cell.

That way I have a list of sizes next to the product description. Th
sizes do not need to be deleted from the product description area.

So A1 would contain "Marlboro 25s" and B1 would contain "25s). And so o
down the list of product descriptions.

Thanks for the help

 

[Claus Busch]

Hi Greg,

Am Fri, 14 Jun 2013 04:40:01 +0100 schrieb GreggyR:

I have a list of products with their sizes all in one cell (for example
"Marlboro 25s"), I would like to search that specific cell for a piece
of text relating to the size (for example "25s") and return just that in
a separate cell.

if your product is always in one word and there is only one space in
front of the size into that cell you can try:
=MID(A1,FIND(" ",A1)+1,10)
If product can be more than one word but the size is always behind the
last space try that function:

Function Sizes(myCell As Range) As String
Dim myStart As Integer
myStart = InStrRev(myCell, " ")
Sizes = Mid(myCell, myStart + 1, 10)
End Function

In B1 write:
=Sizes(A1)


Regards
Claus Busch