Google Docs Editors Help Show Sign in Google Help
Send feedback on... This help content & information General Help Center experience
Google Docs Editors
Daveunread, Oct 26, 2007, 2:03:00 PM10/26/07 to 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 Thanks! Bob Umlasunread, Oct 26, 2007, 2:19:51 PM10/26/07 to if it's always the 4th position, then =mid(A1,4,255). If you want it to be =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20),1)),0),255) Bob Umlas "Dave"
<> wrote in message Farhadunread, Oct 26, 2007, 2:23:03 PM10/26/07 to Hi, Try this: =RIGHT(A1,3) Thanks, Gary''s Studentunread, Oct 26, 2007, 2:27:00 PM10/26/07 to Try this UDF: Function letteronly(r As Range) As String www.exciter.grunread, Oct 26, 2007, 2:44:37 PM10/26/07 to You can try this Custom Function. Copy the code into the VBA window of This function checks each character of your target cell and keeps only Public Function RemoveTexts(Target As Range) Daveunread, Oct 28, 2007, 1:01:00 PM10/28/07 to Thanks Farhad. This works, but my string does not always have 3 digits. Some Daveunread, Oct 28, 2007, 1:04:00 PM10/28/07 to Hi GarysStudent. Can you explain how to use this? Thanks! Daveunread, Oct 28, 2007, 1:04:02 PM10/28/07 to This doesn't seem to work. Daveunread, Oct 28, 2007, 1:03:02 PM10/28/07 to Ragdyerunread, Oct 28, 2007, 1:23:44 PM10/28/07 to Probably because you *didn't* enter it the proper way. You can click in the cell containing the formula. RD --------------------------------------------------------------------------- news:... unread, Oct 28, 2007, 1:45:13 PM10/28/07 to Try... =REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"") Note that the formula will return the number as a text value. To Hope this helps! Gord Dibbenunread, Oct 28, 2007, 1:55:58 PM10/28/07 to 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-click>insert>module. Paste the UDF into that module. Alt + q to return to Excel sheet. Enter the formula as shown.
www.exciter.gr: Custom Excel Applications!unread, Oct 28, 2007, 7:22:10 PM10/28/07 to Dave 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)
5. Go back to your excel sheet, type ABC123 or anything else in Cell Good luck! >
> > Dave- Hide quoted text - Ron Rosenfeldunread, Oct 28, 2007, 9:57:57 PM10/28/07 to On Fri, 26 Oct 2007 11:03:00 -0700, 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}, If the digits are not contiguous, you can use this UDF: ======================= Set re = CreateObject("vbscript.regexp") To enter the UDF, <alt-F11> opens the
VBEditor. Ensure your project is You can then use the formula =Digits(cell_ref) in any cell. e.g. B1: =Digits(A1) Ron Rosenfeldunread, Oct 29, 2007, 6:46:23 AM10/29/07 to On Sun, 28 Oct
2007 21:57:57 -0400, Ron Rosenfeld <> Please note that the formulas I supplied return the digits as a value, and not If you require that the digits be returned as a string, then merely omit the
Daveunread, Oct 29, 2007, 7:56:01 AM10/29/07 to Many thanks for this reply! It does now work. This solution would be very Cheers again Daveunread, Oct 29, 2007, 7:58:00 AM10/29/07 to This formula
does work when enterred as you mentioned. Can you please provide Thanks! RagDyerunread, Oct 29, 2007, 4:08:42 PM10/29/07 to You can start here: http://www.cpearson.com/excel/ArrayFormulas.aspx -- RD --------------------------------------------------------------------------- news:... Daveunread, Oct 29, 2007, 4:50:03 PM10/29/07 to Is there a way to do something
similar with IsText so that ABC is left, and Thanks! Ron Rosenfeldunread, Oct 29, 2007, 5:01:21 PM10/29/07 to On Mon, 29 Oct 2007 13:50:03 -0700, Dave <> >Is there a way to do something similar
with IsText so that ABC is left, and You could use this UDF, which will return either Text or Digits depending on ==========================
If Txt = True Then Set re = CreateObject("vbscript.regexp") TextOrDigits = re.Replace(str, sRes) So to return just Text: =TextOrDigits(A1) and to return just digits: =TextOrDigits(A1,False)
Johnny Bravounread, Aug 3, 2010, 4:53:48 PM8/3/10 to I
just wanted to thank you for this VB code. P.S. sorry for my poor english www.exciter.gr wrote: You can try this Custom Function. 26-Oct-07 You can try this Custom Function. Copy the code into the VBA
window of This function checks each character of your target cell and keeps only Public Function RemoveTexts(Target As Range) Previous Posts In This Thread: On Friday, October 26, 2007 2:03 PM Remove Numbers from Alphanumeric String 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 Thanks! On Friday, October 26, 2007 2:19 PM if it's always the 4th position, then =mid(A1,4,255). =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20),1)),0),255) Bob
Umlas On Friday, October 26, 2007 2:23 PM RE: Remove Numbers from Alphanumeric String Try this: =RIGHT(A1,3) Thanks,
On Friday, October 26, 2007 2:27 PM RE: Remove Numbers from Alphanumeric String Function letteronly(r As Range) As String
On Friday, October 26, 2007 2:44 PM You can try this
Custom Function. This function checks each character of your target cell and keeps only Public Function RemoveTexts(Target As Range) On Sunday, October 28, 2007 1:01 PM Thanks Farhad. This works, but my string does not always have 3 digits. "Farhad" wrote: On
Sunday, October 28, 2007 1:03 PM Re: Remove Numbers from Alphanumeric String "Bob Umlas" wrote: On Sunday, October 28, 2007 1:04 PM Hi GarysStudent. Can you explain how to use this? "Gary''s Student" wrote: On Sunday, October 28, 2007 1:04 PM Re: Remove Numbers from Alphanumeric String "www.exciter.gr" wrote: On Sunday, October 28, 2007 1:23 PM Probably because you *didn't* enter it the proper way.It's an *array* formula! You can click in the cell containing the formula. RD --------------------------------------------------------------------------- be On Sunday, October 28, 2007 1:45 PM Re: Remove Numbers from Alphanumeric String Try... =REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"") Note that the formula will return the number as a text value. To Hope this helps! On Sunday, October 28, 2007 1:55 PM Works for me.Where did you store the code? 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-click>insert>module. Paste the UDF into that module. Alt + q to return to Excel sheet. Enter the formula as shown. On Sunday, October 28, 2007 7:22
PM Davejust 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) Public Function RemoveTexts(Target As Range) 5. Go back to your excel sheet, type ABC123 or anything else in Cell
Good luck! On Sunday, October 28, 2007 9:57 PM Re: Remove Numbers from Alphanumeric String 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}, If the digits are not contiguous, you can use this UDF: ======================= Const sRes As String = "" Set re = CreateObject("vbscript.regexp")
Digits = re.Replace(str, sRes) To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is You can then use the formula =Digits(cell_ref) in any cell. e.g. B1: =Digits(A1) On
Monday, October 29, 2007 6:46 AM Re: Remove Numbers from Alphanumeric String Please note that the formulas I supplied return the digits as a value, and not If you require that the digits be returned as a string, then merely omit the
On Monday, October 29, 2007 7:56
AM Many thanks for this reply! Cheers again On Monday, October 29, 2007 7:58 AM This formula does work when enterred as you mentioned. Thanks! "Ragdyer" wrote: On Monday, October 29, 2007 4:08 PM Re: Remove Numbers from Alphanumeric String http://www.cpearson.com/excel/ArrayFormulas.aspx -- RD --------------------------------------------------------------------------- On Monday, October 29, 2007 4:50 PM Is there a way to do something similar with IsText so that ABC is left, and Thanks! "www.exciter.gr: Custom Excel Application" wrote: On Monday, October 29, 2007 5:01 PM Re: Remove Numbers from Alphanumeric String =TextOrDigits(A1) =TextOrDigits(A1,False)
unread, Dec 21, 2016, 1:25:46 PM12/21/16 to Maybe this will help? =SPLIT( LOWER($A1) , "abcdefghijklmnopqrstuvwxyz -()/|\!@#$%^&*<>~`[]}{:;?.,()" ) How do I remove numbers from a cell?Select a blank cell that you will return the text string without numbers, enter the formula =RemoveNumbers(A2) (A2 is the cell you will remove numbers from), and then drag the Fill Handle down to the range as you need.
How do I get rid of extra digits in Google Sheets?How to remove unwanted spaces and characters. Go to Extensions > Power Tools > Start to open the add-on in Google Sheets:. Access the Text group on the add-on sidebar:. Click on the Remove icon to run the tool:. Select the range with your data and choose between three ways of clearing the selected range.. |