NEED HELP for getting text from cell

[rajtak]

Hi,

I need your help to get some text right before some common text in the cell.
E.g.

if
Cell A1 contains ABCD 36 IN
Cell A2 contains ABCDEFGH 48 IN XX
Cell A3 contains ABCD XX XXX 22 IN HHH

I need that value before the IN i.e.

Cell B1 it should be 36
Cell B2 it should be 48
Cell B3 it should be 22

Please suggest any formula to get this...

 

[Michael M]

Hi Raj
If you only want to extract numbers froma text string try this Function

Function ExtractNumber(rCell As Range)
Dim lCount As Long, l As Long
Dim sText As String
Dim lNum As String

sText = rCell
For lCount = Len(sText) To 1 Step -1
    If IsNumeric(Mid(sText, lCount, 1)) Then
        l = l + 1
        lNum = Mid(sText, lCount, 1) & lNum
    End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount

ExtractNumber = CLng(lNum)
End Function

so, to extract numbers from a string in sya, cell A1....type extractnumber(A1) in B1

Regards
Michael M

 

[sanrv1f]

Try this

Excel Workbook
	A	B
1	ABCD 36 IN	36
2	ABCDEFGH 48 IN XX	48
3	ABCD XX XXX 22 IN HHH	22
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=-LOOKUP(0,-RIGHT(LEFT(A1,SEARCH("in",A1)-2),ROW(OFFSET($A$1,,,LEN(A1)))))

 

[rajtak]

Actually there are numbers also in the cells

E.g.

Cell A1 ABCDE 45 XXX 68 IN
Cell A2 ABCDE XXX 58 SS 52 IN XXX
Cell A3 ABCDE 45 55 IN XX

value that i need are the inches that are given before 'IN'

Cell B1 68
Cell B2 52
Cell B3 55

 

[rajtak]

@Sankar

Thanx Machi!!!! Works like a charm