Extract Text from a cell.

[kashif.special2005]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I am Kashif, I have a column A, in this column there is alpha numeric values in every cell, like below I want to extract only text from the cell.
<o> </o>
Intell54768 com : Intellcom (there is space between number and com)
Renewal874658University : RenewalUniversity (There is not no any space)
<o> </o>
Thanks,
Kashif.<o></o>

 

[T. Valko]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am Kashif, I have a column A, in this column there is alpha numeric values in every cell, like below I want to extract only text from the cell.
<o></o>
Intell54768 com : Intellcom (there is space between number and com)
Renewal874658University : RenewalUniversity (There is not no any space)
<o></o>
Thanks,
Kashif.<o></o>

This works on your posted sample data.

Book1
	A	B
2	Intell54768 com	Intellcom
3	Renewal874658University	RenewalUniversity
Sheet1

Formula entered in B2 and copied down:

=SUBSTITUTE(A2,MID(A2,MIN(FIND({" ",0,1,2,3,4,5,6,7,8,9},A2&" 0123456789")),SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{" ",0,1,2,3,4,5,6,7,8,9},"")))),"")

 

[kashif.special2005]

Thank you so much......

 

[kashif.special2005]

Hi T. Valko,

I am sorry, but there is some other words also like below

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=202 border=0 x:str><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7387" width=202><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 152pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=202 height=17>Bharti8745 Airtel 86 New Delhi</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Reliance5698 Relecom NewDelhi</TD></TR></TBODY></TABLE>

I want to extract only alpha characters only from a alpha numeric cells.

Thanks,
Kashif.

 

[T. Valko]

Hi T. Valko,

I am sorry, but there is some other words also like below

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=202 border=0 x:str><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7387" width=202><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 152pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=202 height=17>Bharti8745 Airtel 86 New Delhi</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Reliance5698 Relecom NewDelhi</TD></TR></TBODY></TABLE>

I want to extract only alpha characters only from a alpha numeric cells.

Thanks,
Kashif.

You probably need a UDF (VBA User Defined Function) to do that.

Someone else will need to help you with that.

 

[Scott Huish]

Here you go:

Function AlphaNums(r As String) As Long
With CreateObject("vbscript.regexp")
    .Pattern = "([A-Za-z]+)(\d+)"
    If .test(r) Then AlphaNums = Val(.Replace(.Execute(r)(0), "$2"))
End With
End Function

Excel Workbook
	A	B
1	Bharti8745 Airtel 86 New Delhi	8745
2	Reliance5698 Relecom NewDelhi	5698
3	Renewal874658University : RenewalUniversity	874658
Sheet1

 

[T. Valko]

Here you go:

Function AlphaNums(r As String) As Long
With CreateObject("vbscript.regexp")
    .Pattern = "([A-Za-z]+)(\d+)"
    If .test(r) Then AlphaNums = Val(.Replace(.Execute(r)(0), "$2"))
End With
End Function

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 272px"><COL style="WIDTH: 266px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Bharti8745 Airtel 86 New Delhi</TD><TD style="TEXT-ALIGN: right">8745</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Reliance5698 Relecom NewDelhi</TD><TD style="TEXT-ALIGN: right">5698</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Renewal874658University : RenewalUniversity</TD><TD style="TEXT-ALIGN: right">874658</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=AlphaNums(A1)</TD></TR><TR><TD>B2</TD><TD>=AlphaNums(A2)</TD></TR><TR><TD>B3</TD><TD>=AlphaNums(A3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I think they want to keep the TEXT.

Strip the numbers and keep the text.

I want to extract only text from the cell.

 

[markmzz]

Try this code:

Function mySubstitute(myString As String) As String
    Dim myChar As Variant
    Dim mySubstituteString As String
 
    mySubstituteString = myString
 
    For Each myChar In Array(" ", 0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
        mySubstituteString = Replace(mySubstituteString, myChar, "")
    Next myChar
 
    mySubstitute = mySubstituteString
 
End Function

Markmzz

 

[Scott Huish]

Function AlphaOnly(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "\d"
    .Global = True
    AlphaOnly = .Replace(r, "")
End With
End Function

Excel Workbook
	A	B
1	Bharti8745 Airtel 86 New Delhi	Bharti Airtel New Delhi
2	Reliance5698 Relecom NewDelhi	Reliance Relecom NewDelhi
3	Renewal874658University : RenewalUniversity	RenewalUniversity : RenewalUniversity
Sheet1

 

[kashif.special2005]

Thank you very much guys, you guys are genius...........

Just I want to know that can we extract only text through formula or Array formula...........

Thanks,
Kashif.