Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 26th 07, 07:03 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

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

  #2   Report Post  
Old October 26th 07, 07:19 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 68
Default Remove Numbers from Alphanumeric String

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


  #3   Report Post  
Old October 26th 07, 07:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 281
Default Remove Numbers from Alphanumeric String

Hi,

Try this:

=RIGHT(A1,3)

Thanks,
--
Farhad Hodjat


"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

  #4   Report Post  
Old October 26th 07, 07:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default Remove Numbers from Alphanumeric String

Try this UDF:

Function letteronly(r As Range) As String
letteronly = ""
If Application.WorksheetFunction.IsText(r.Value) Then
s = r.Value
For i = 49 To 57
s = Replace(s, Chr(i), "")
Next i
letteronly = s
End If
End Function
--
Gary''s Student - gsnu200751


"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

  #5   Report Post  
Old October 26th 07, 07:44 PM posted to microsoft.public.excel.misc
Banned
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 4
Default Remove Numbers from Alphanumeric String

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





  #6   Report Post  
Old October 28th 07, 06:01 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

Thanks Farhad. This works, but my string does not always have 3 digits. Some
are 4 or 5 digits.

"Farhad" wrote:

Hi,

Try this:

=RIGHT(A1,3)

Thanks,
--
Farhad Hodjat


"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

  #7   Report Post  
Old October 28th 07, 06:03 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

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


  #8   Report Post  
Old October 28th 07, 06:04 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

Hi GarysStudent. Can you explain how to use this? Thanks!

"Gary''s Student" wrote:

Try this UDF:

Function letteronly(r As Range) As String
letteronly = ""
If Application.WorksheetFunction.IsText(r.Value) Then
s = r.Value
For i = 49 To 57
s = Replace(s, Chr(i), "")
Next i
letteronly = s
End If
End Function
--
Gary''s Student - gsnu200751


"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

  #9   Report Post  
Old October 28th 07, 06:04 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

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




  #10   Report Post  
Old October 28th 07, 06:23 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

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





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 08: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 06:55 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