Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old October 28th 07, 05:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 14
Default Remove Numbers from Alphanumeric String

On Oct 26, 2:03 pm, Dave wrote:
Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.

Thanks!
Dave


Try...

=REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))-1,"")

Note that the formula will return the number as a text value. To
return the number as a numerical value, add +0 at the end of the
formula.

Hope this helps!


  #12   Report Post  
Old October 28th 07, 05:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
Default Remove Numbers from Alphanumeric String

Works for me.

Where did you store the code?

Copy and paste into a general module in your workbook.

Alt + F11 to go to Visual Basic Editor.

CTRL + r to open Project Explorer window.

Select your workbook/project and right-clickinsertmodule.

Paste the UDF into that module.

Alt + q to return to Excel sheet.

Enter the formula as shown.


Gord Dibben MS Excel MVP


On Sun, 28 Oct 2007 10:04:02 -0700, Dave wrote:

This doesn't seem to work.

"www.exciter.gr" wrote:

You can try this Custom Function. Copy the code into the VBA window of
your file and then go to cell B1 and type =RemoveTexts(A1)

This function checks each character of your target cell and keeps only
numeric characters. Before returning the number, it actually converts
it to numeric (so 123 will be number, not text). For empty or text-
only cells, it will return zero.

Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function





On Oct 26, 9:03 pm, Dave wrote:
Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.

Thanks!
Dave





  #13   Report Post  
Old October 28th 07, 11:22 PM posted to microsoft.public.excel.misc
Banned
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 19
Default Remove Numbers from Alphanumeric String

Dave
just tried my solution again and it works fine.

Please follow the procedure step by step to make it work for you too:

1. Open your excel file (suppose its name is yourfile.xls)
2. Go to Tools/Macro/Visual Basic Editor
3. Right click on the line VBAProject(yourfile.xls) on the left list
and click on Insert Module
4. Paste the code into the white window on the right:
Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function

5. Go back to your excel sheet, type ABC123 or anything else in Cell
A1
6. Type the formula: "=RemoveTexts(A1)" without the quotes into Cell
B1 and press enter
7. It should return the correct result

Good luck!



On Oct 28, 7:04 pm, Dave wrote:
This doesn't seem to work.



"www.exciter.gr" wrote:
You can try this Custom Function. Copy the code into the VBA window of
your file and then go to cell B1 and type =RemoveTexts(A1)


This function checks each character of your target cell and keeps only
numeric characters. Before returning the number, it actually converts
it to numeric (so 123 will be number, not text). For empty or text-
only cells, it will return zero.


Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function


On Oct 26, 9:03 pm, Dave wrote:
Hi,


I have a value in Cell A of ABC123.


I want Cell B1 to contain the 123 from this cell.


Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.


Thanks!
Dave- Hide quoted text -


- Show quoted text -



  #14   Report Post  
Old October 29th 07, 01:57 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Remove Numbers from Alphanumeric String

On Fri, 26 Oct 2007 11:03:00 -0700, Dave
wrote:

Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.

Thanks!
Dave


Assuming the digits are all contiguous:

B1:

=LOOKUP(9.99E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

If the digits are not contiguous, you can use this UDF:

=======================
Option Explicit
Function Digits(str As String)
Dim re As Object
Const sPat As String = "\D"
Const sRes As String = ""

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
Digits = re.Replace(str, sRes)
If IsNumeric(Digits) Then Digits = CDbl(Digits)
End Function
===========================

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code above into the window that opens.

You can then use the formula =Digits(cell_ref) in any cell. e.g.

B1: =Digits(A1)
--ron
  #15   Report Post  
Old October 29th 07, 10:46 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Remove Numbers from Alphanumeric String

On Sun, 28 Oct 2007 21:57:57 -0400, Ron Rosenfeld
wrote:

On Fri, 26 Oct 2007 11:03:00 -0700, Dave
wrote:

Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.

Thanks!
Dave


Assuming the digits are all contiguous:

B1:

=LOOKUP(9.99E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

If the digits are not contiguous, you can use this UDF:

=======================
Option Explicit
Function Digits(str As String)
Dim re As Object
Const sPat As String = "\D"
Const sRes As String = ""

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
Digits = re.Replace(str, sRes)
If IsNumeric(Digits) Then Digits = CDbl(Digits)
End Function
===========================

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code above into the window that opens.

You can then use the formula =Digits(cell_ref) in any cell. e.g.

B1: =Digits(A1)
--ron


Please note that the formulas I supplied return the digits as a value, and not
as a string. What this means is that leading 0's will get dropped.

If you require that the digits be returned as a string, then merely omit the
next to last line in the UDF (The line that starts with " If IsNumeric..."


--ron


  #16   Report Post  
Old October 29th 07, 11:56 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,389
Default Remove Numbers from Alphanumeric String

Many thanks for this reply! It does now work. This solution would be very
useful for anyone trying to achieve a similar result!!

Cheers again
Dave

"www.exciter.gr: Custom Excel Application" wrote:

Dave
just tried my solution again and it works fine.

Please follow the procedure step by step to make it work for you too:

1. Open your excel file (suppose its name is yourfile.xls)
2. Go to Tools/Macro/Visual Basic Editor
3. Right click on the line VBAProject(yourfile.xls) on the left list
and click on Insert Module
4. Paste the code into the white window on the right:
Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function

5. Go back to your excel sheet, type ABC123 or anything else in Cell
A1
6. Type the formula: "=RemoveTexts(A1)" without the quotes into Cell
B1 and press enter
7. It should return the correct result

Good luck!



On Oct 28, 7:04 pm, Dave wrote:
This doesn't seem to work.



"www.exciter.gr" wrote:
You can try this Custom Function. Copy the code into the VBA window of
your file and then go to cell B1 and type =RemoveTexts(A1)


This function checks each character of your target cell and keeps only
numeric characters. Before returning the number, it actually converts
it to numeric (so 123 will be number, not text). For empty or text-
only cells, it will return zero.


Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function


On Oct 26, 9:03 pm, Dave wrote:
Hi,


I have a value in Cell A of ABC123.


I want Cell B1 to contain the 123 from this cell.


Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.


Thanks!
Dave- Hide quoted text -


- Show quoted text -




  #17   Report Post  
Old October 29th 07, 11:58 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,389
Default Remove Numbers from Alphanumeric String

This formula does work when enterred as you mentioned. Can you please provide
some info on what this CSE method is and why it is used. It is new to me.

Thanks!
Dave

"Ragdyer" wrote:

Probably because you *didn't* enter it the proper way.
It's an *array* formula!
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

You can click in the cell containing the formula.
Then click in the formula bar, hold down
<Ctrl and <Shift
Then hit <Enter
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave" wrote in message
...
This returns #N/A

"Bob Umlas" wrote:

if it's always the 4th position, then =mid(A1,4,255). If you want it to

be
numeric, then =1*mid(a1,4,255).
If the position is unknown but is always letters followed by numbers,
ctrl+shift+enter this:

=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20) ,1)),0),255)

Bob Umlas
Excel MVP

"Dave" wrote in message
...
Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.

Thanks!
Dave



  #18   Report Post  
Old October 29th 07, 08:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default Remove Numbers from Alphanumeric String

You can start he

http://www.cpearson.com/excel/ArrayFormulas.aspx

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave" wrote in message
news
This formula does work when enterred as you mentioned. Can you please
provide
some info on what this CSE method is and why it is used. It is new to me.

Thanks!
Dave

"Ragdyer" wrote:

Probably because you *didn't* enter it the proper way.
It's an *array* formula!
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

You can click in the cell containing the formula.
Then click in the formula bar, hold down
<Ctrl and <Shift
Then hit <Enter
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave" wrote in message
...
This returns #N/A

"Bob Umlas" wrote:

if it's always the 4th position, then =mid(A1,4,255). If you want it
to

be
numeric, then =1*mid(a1,4,255).
If the position is unknown but is always letters followed by numbers,
ctrl+shift+enter this:

=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20) ,1)),0),255)

Bob Umlas
Excel MVP

"Dave" wrote in message
...
Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen
it
somewhere before but can't put my finger on it.

Thanks!
Dave





  #19   Report Post  
Old October 29th 07, 08:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,389
Default Remove Numbers from Alphanumeric String

Is there a way to do something similar with IsText so that ABC is left, and
123 is removed?

Thanks!
Dave

"www.exciter.gr: Custom Excel Application" wrote:

Dave
just tried my solution again and it works fine.

Please follow the procedure step by step to make it work for you too:

1. Open your excel file (suppose its name is yourfile.xls)
2. Go to Tools/Macro/Visual Basic Editor
3. Right click on the line VBAProject(yourfile.xls) on the left list
and click on Insert Module
4. Paste the code into the white window on the right:
Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function

5. Go back to your excel sheet, type ABC123 or anything else in Cell
A1
6. Type the formula: "=RemoveTexts(A1)" without the quotes into Cell
B1 and press enter
7. It should return the correct result

Good luck!



On Oct 28, 7:04 pm, Dave wrote:
This doesn't seem to work.



"www.exciter.gr" wrote:
You can try this Custom Function. Copy the code into the VBA window of
your file and then go to cell B1 and type =RemoveTexts(A1)


This function checks each character of your target cell and keeps only
numeric characters. Before returning the number, it actually converts
it to numeric (so 123 will be number, not text). For empty or text-
only cells, it will return zero.


Public Function RemoveTexts(Target As Range)
Dim t As String
For i = 1 To Len(Target.Value)
t = Mid(Target.Value, i, 1)
If IsNumeric(t) = True Then
RemoveTexts = RemoveTexts & t
End If
Next i
RemoveTexts = Val(RemoveTexts)
End Function


On Oct 26, 9:03 pm, Dave wrote:
Hi,


I have a value in Cell A of ABC123.


I want Cell B1 to contain the 123 from this cell.


Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.


Thanks!
Dave- Hide quoted text -


- Show quoted text -




  #20   Report Post  
Old October 29th 07, 09:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Remove Numbers from Alphanumeric String

On Mon, 29 Oct 2007 13:50:03 -0700, Dave
wrote:

Is there a way to do something similar with IsText so that ABC is left, and
123 is removed?

Thanks!
Dave


You could use this UDF, which will return either Text or Digits depending on
the setting of the second (optional) argument:

==========================
Option Explicit
Function TextOrDigits(str As String, Optional Txt As Boolean = True)
Dim re As Object
Dim sPat As String
Const sRes As String = ""

If Txt = True Then
sPat = "\d"
Else
sPat = "\D"
End If

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
TextOrDigits = re.Replace(str, sRes)
End Function
=================================

So to return just Text:

=TextOrDigits(A1)
or
=TextOrDigits(A1,True)

and to return just digits:

=TextOrDigits(A1,False)


--ron


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparison of alphanumeric string ErExcel Excel Discussion (Misc queries) 4 May 7th 07 02:14 PM
How to extract decimal numbers e.g. $1.57 from alphanumeric string Lio Excel Discussion (Misc queries) 8 December 12th 06 07:35 PM
How do I replace last numeric string from a alphanumeric string? Christy Excel Discussion (Misc queries) 3 August 11th 06 12:17 AM
Auto convert an alphanumeric string (CIS9638S) to numbers only? SDesmond Excel Worksheet Functions 0 September 7th 05 01:17 AM
Auto convert an alphanumeric string (CIS9638S) to numbers only? Gary L Brown Excel Worksheet Functions 1 September 7th 05 01:17 AM


All times are GMT +1. The time now is 09:43 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017