NEED HELP for getting text from cell

rajtak

Board Regular
Joined
Feb 23, 2009
Messages
74
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
Joined
Oct 27, 2005
Messages
18,204
Office Version
2013
Platform
Windows
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
Joined
Jan 1, 2009
Messages
3,468
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
Joined
Feb 23, 2009
Messages
74
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
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top