Google sheets remove numbers from cell

Google Docs Editors Help

Sign in

Google Help

  • Help Center
  • Community
  • Google Docs Editors
  • Privacy Policy
  • Terms of Service
  • Submit feedback

Send feedback on...

This help content & information

General Help Center experience

  • Help Center
  • Community

Google Docs Editors

Dave

unread,

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
somewhere before but can't put my finger on it.

Thanks!
Dave

Bob Umlas

unread,

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
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
news:...

Farhad

unread,

Oct 26, 2007, 2:23:03 PM10/26/07

to

Hi,

Try this:

=RIGHT(A1,3)

Thanks,
--
Farhad Hodjat

Gary''s Student

unread,

Oct 26, 2007, 2:27:00 PM10/26/07

to

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

www.exciter.gr

unread,

Oct 26, 2007, 2:44:37 PM10/26/07

to

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

Dave

unread,

Oct 28, 2007, 1:01:00 PM10/28/07

to

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

Dave

unread,

Oct 28, 2007, 1:04:00 PM10/28/07

to

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

Dave

unread,

Oct 28, 2007, 1:04:02 PM10/28/07

to

This doesn't seem to work.

Dave

unread,

Oct 28, 2007, 1:03:02 PM10/28/07

to

Ragdyer

unread,

Oct 28, 2007, 1:23:44 PM10/28/07

to

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

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
return the number as a numerical value, add +0 at the end of the
formula.

Hope this helps!

Gord Dibben

unread,

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.


Gord Dibben MS Excel MVP

www.exciter.gr: Custom Excel Applications!

unread,

Oct 28, 2007, 7:22:10 PM10/28/07

to

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!

> > > Dave- Hide quoted text -
>
> - Show quoted text -

Ron Rosenfeld

unread,

Oct 28, 2007, 9:57:57 PM10/28/07

to

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

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

Ron Rosenfeld

unread,

Oct 29, 2007, 6:46:23 AM10/29/07

to

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

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

Dave

unread,

Oct 29, 2007, 7:56:01 AM10/29/07

to

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

Dave

unread,

Oct 29, 2007, 7:58:00 AM10/29/07

to

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

unread,

Oct 29, 2007, 4:08:42 PM10/29/07

to

You can start here:

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:...

Dave

unread,

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
123 is removed?

Thanks!
Dave

Ron Rosenfeld

unread,

Oct 29, 2007, 5:01:21 PM10/29/07

to

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

Johnny Bravo

unread,

Aug 3, 2010, 4:53:48 PM8/3/10

to

I just wanted to thank you for this VB code.
It's just what i needed.

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

Previous Posts In This Thread:

On Friday, October 26, 2007 2:03 PM
Dav wrote:

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

On Friday, October 26, 2007 2:19 PM
Bob Umlas wrote:

if it's always the 4th position, then =mid(A1,4,255).
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

On Friday, October 26, 2007 2:23 PM
fdh wrote:

RE: Remove Numbers from Alphanumeric String
Hi,

Try this:

=RIGHT(A1,3)

Thanks,
--
Farhad Hodjat


"Dave" wrote:

On Friday, October 26, 2007 2:27 PM
GarysStuden wrote:

RE: 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:

On Friday, October 26, 2007 2:44 PM
www.exciter.gr wrote:

You can try this Custom Function.
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 Sunday, October 28, 2007 1:01 PM
Dav wrote:

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

"Farhad" wrote:

On Sunday, October 28, 2007 1:03 PM
Dav wrote:

Re: Remove Numbers from Alphanumeric String
This returns #N/A

"Bob Umlas" wrote:

On Sunday, October 28, 2007 1:04 PM
Dav wrote:

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

"Gary''s Student" wrote:

On Sunday, October 28, 2007 1:04 PM
Dav wrote:

Re: Remove Numbers from Alphanumeric String
This does not seem to work.

"www.exciter.gr" wrote:

On Sunday, October 28, 2007 1:23 PM
Ragdyer wrote:

Probably because you *didn't* enter it the proper way.It's an *array* formula!
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
news:...

be

On Sunday, October 28, 2007 1:45 PM
domenic2 wrote:

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
return the number as a numerical value, add +0 at the end of the
formula.

Hope this helps!

On Sunday, October 28, 2007 1:55 PM
Gord Dibben wrote:

Works for me.Where did you store the code?
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.

On Sunday, October 28, 2007 7:22 PM
www.exciter.gr: Custom Excel Applications! wrote:

Davejust tried my solution again and it works fine.


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 Sunday, October 28, 2007 9:57 PM
Ron Rosenfeld wrote:

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},
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

On Monday, October 29, 2007 6:46 AM
Ron Rosenfeld wrote:

Re: Remove Numbers from Alphanumeric String

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

On Monday, October 29, 2007 7:56 AM
Dav wrote:

Many thanks for this reply!
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

On Monday, October 29, 2007 7:58 AM
Dav wrote:

This formula does work when enterred as you mentioned.
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:

On Monday, October 29, 2007 4:08 PM
RagDyer wrote:

Re: Remove Numbers from Alphanumeric String
You can start here:

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:...

On Monday, October 29, 2007 4:50 PM
Dav wrote:

Is there a way to do something similar with IsText so that ABC is left, and
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:

On Monday, October 29, 2007 5:01 PM
Ron Rosenfeld wrote:

Re: Remove Numbers from Alphanumeric String

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

=TextOrDigits(A1,False)


--ron


Submitted via EggHeadCafe - Software Developer Portal of Choice
Custom Favorites Web Site with MongoDb and NoRM
http://www.eggheadcafe.com/tutorials/aspnet/7fbc7a01-5d30-4cd3-b373-51d4a0e1afa8/custom-favorites-web-site-with-mongodb-and-norm.aspx

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