Searching for text and returning text
 

Hello

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.

That way I have a list of sizes next to the product description. The 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 on down the list of product descriptions.

Thanks for the help.

Searching for text and returning text
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2