Extract Alphabets and Numbers from a Cell

T

tahir4awan

I want to make a formula so that it can extract alphabets and number
from a string containing both alphabets and numbers
 
C

Claus Busch

Hi,

Am Sun, 21 Oct 2012 03:24:08 +0000 schrieb tahir4awan:
I want to make a formula so that it can extract alphabets and numbers
from a string containing both alphabets and numbers.
Click to expand...

where is the number in the string? Left, right, middle? Please post an
example of your strings.


Regards
Claus Busch
 
T

tahir4awan

Claus said:
Hi,

Am Sun, 21 Oct 2012 03:24:08 +0000 schrieb tahir4awan:
-

where is the number in the string? Left, right, middle? Please post an
example of your strings.


Regards
Claus Busch
Click to expand...

Here is the picture of the functio

+-------------------------------------------------------------------
|Filename: untitled.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=639
+-------------------------------------------------------------------
 
C

Claus Busch

Hi,

Am Mon, 22 Oct 2012 10:24:35 +0000 schrieb tahir4awan:
Here is the picture of the function

+-------------------------------------------------------------------+
|Filename: untitled.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=639|
+-------------------------------------------------------------------+
Click to expand...

in C2 try:
=--(MID(LEFT(A2,MAX(ISNUMBER(MID(A2,COLUMN(2:2),1)*1)*COLUMN(2:2))),MATCH(1,ISNUMBER(MID(A2&0,COLUMN(2:2),1)*1)*1,0),LEN(A2)))
and in B2 try:
=SUBSTITUTE(A2,C2,)

if the number starts always as 4. digit and is always 3 digits long,
then easier in C2:
=--MID(A2,4,3)
and B2:
=SUBSTITUTE(A2,C2,)


Regards
Claus Busch
 
C

Claus Busch

Hi,

Am Mon, 22 Oct 2012 15:59:07 +0200 schrieb Claus Busch:
in C2 try:
=--(MID(LEFT(A2,MAX(ISNUMBER(MID(A2,COLUMN(2:2),1)*1)*COLUMN(2:2))),MATCH(1,ISNUMBER(MID(A2&0,COLUMN(2:2),1)*1)*1,0),LEN(A2)))
Click to expand...

this is an array formula to enter with CTRL+Shift+Enter


Regards
Claus Busch
 
G

Gord Dibben

Try a UDF

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function


Function RemDigits(str As String) As String
'Remove numbers from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function


Gord
 
R

Ron Rosenfeld

Try a UDF

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function


Function RemDigits(str As String) As String
'Remove numbers from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function


Gord
Click to expand...

Just a few points, depending, of course, on what the OP really means. My guess is that your routines will satisfy his requirements, but I'm in a nit-pickey mood today :))

I assume by "alphabets" he means [A-Za-z].

Your first expression will remove all non-digits, not just the "alphabets".
Your second expression will remove all digits, leaving not only the "alphabets" but also various special characters.

So, the \D+ will serve to return all the digits.

But \d+ will return all non-digits, which can include punctuation, etc.

To only return the "alphabets" I would suggest [^A-Za-z]+
 
Top