I have searched so many websites but couldn't find any solution to change the font size of a combo box. Some say that you cannot change font size but if you want to change font size you have to make a combo box from VB coding.

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.

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Here is the picture of the function

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|
+-------------------------------------------------------------------+

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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)))

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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




On Sun, 21 Oct 2012 03:24:08 +0000, tahir4awan
wrote:


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

On Tue, 23 Oct 2012 09:38:19 -0700, Gord Dibben wrote:

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





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]+