# NEED HELP for getting text from cell

#### rajtak

##### Board Regular
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

##### Well-known Member
Hi Raj
If you only want to extract numbers froma text string try this Function
Code:
``````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

##### MrExcel MVP
Try this
Excel Workbook
AB
1ABCD 36 IN36
2ABCDEFGH 48 IN XX48
3ABCD XX XXX 22 IN HHH22
Sheet1
Excel 2003
Cell Formulas
Range(s)Formula
B1=-LOOKUP(0,-RIGHT(LEFT(A1,SEARCH("in",A1)-2),ROW(OFFSET(\$A\$1,,,LEN(A1)))))

#### rajtak

##### Board Regular
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

##### Board Regular
@Sankar

Thanx Machi!!!! Works like a charm