Deleting numerical values within a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column with numbers and text - I want to delete all numerical values
and leave the text intact. Any help would be greatly appreciated.
 
Try this:

Select the range of cells to be impacted
Press the [F5] key........a shortcut for <edit><go to>
Click [Special]
Select: Formulas.....Uncheck: Text
Click [OK]

That will select all of the numeric values (and errors)
Press the [Delete] key to erase the contents of the selected cells

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
You will need VBA for this:

Function stripNumbers(v) As String
s = ""
For i = 1 To Len(v)
If Asc(Mid(v, i, 1)) < 48 Or Asc(Mid(v, i, 1)) > 57 Then
s = s & Mid(v, i, 1)
End If
Next i
stripNumbers = s
End Function

Now, if A1 contains the mixed text, you can use

=stripNumbers(A1)

HTH
Kostis Vezerides
 
Say column A has numbers in some cells and text in other cells. In B1 enter:

=IF(ISNUMBER(A1),"",A1) and copy down. Then copy column B and
paste/special/value back onto column A
 
Oops,
I just realized, seeing Ron's solution and rereading the OP that I
gave an answer to another problem.
Please follow Ron's suggestion. Or use an extra column with either

=ISNUMBER(A2)
=ISTEXT(A2)

And filter accordingly on TRUE or FALSE

HTH
Kostis
 
Ron - it looks like it should work but I keep getting "no cells found" - The
cells I am looking for look like xxxxxxxxx, 131414265, xxxxxxxx - I want to
simply delete the numbers. thanks

Ron Coderre said:
Try this:

Select the range of cells to be impacted
Press the [F5] key........a shortcut for <edit><go to>
Click [Special]
Select: Formulas.....Uncheck: Text
Click [OK]

That will select all of the numeric values (and errors)
Press the [Delete] key to erase the contents of the selected cells

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
I have a column with numbers and text - I want to delete all numerical
values
and leave the text intact. Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
I need more information.....

Are you saying that one cell contains this kind of text?:
xxxxxxxxx, 131414265, xxxxxxxx

and you want to adjust it to contain (all in the same cell):
xxxxxxxxx, , xxxxxxxx

or is your situation different?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
Ron - it looks like it should work but I keep getting "no cells found" -
The
cells I am looking for look like xxxxxxxxx, 131414265, xxxxxxxx - I want
to
simply delete the numbers. thanks

Ron Coderre said:
Try this:

Select the range of cells to be impacted
Press the [F5] key........a shortcut for <edit><go to>
Click [Special]
Select: Formulas.....Uncheck: Text
Click [OK]

That will select all of the numeric values (and errors)
Press the [Delete] key to erase the contents of the selected cells

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
I have a column with numbers and text - I want to delete all numerical
values
and leave the text intact. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Yes (ideally without the double commas).

Ron Coderre said:
I need more information.....

Are you saying that one cell contains this kind of text?:
xxxxxxxxx, 131414265, xxxxxxxx

and you want to adjust it to contain (all in the same cell):
xxxxxxxxx, , xxxxxxxx

or is your situation different?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
Ron - it looks like it should work but I keep getting "no cells found" -
The
cells I am looking for look like xxxxxxxxx, 131414265, xxxxxxxx - I want
to
simply delete the numbers. thanks

Ron Coderre said:
Try this:

Select the range of cells to be impacted
Press the [F5] key........a shortcut for <edit><go to>
Click [Special]
Select: Formulas.....Uncheck: Text
Click [OK]

That will select all of the numeric values (and errors)
Press the [Delete] key to erase the contents of the selected cells

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

I have a column with numbers and text - I want to delete all numerical
values
and leave the text intact. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Are your numbers always surrounded by commas like you showed? Can there be
more than one number in your text?

Rick

thd3 said:
Yes (ideally without the double commas).

Ron Coderre said:
I need more information.....

Are you saying that one cell contains this kind of text?:
xxxxxxxxx, 131414265, xxxxxxxx

and you want to adjust it to contain (all in the same cell):
xxxxxxxxx, , xxxxxxxx

or is your situation different?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
Ron - it looks like it should work but I keep getting "no cells
found" -
The
cells I am looking for look like xxxxxxxxx, 131414265, xxxxxxxx - I
want
to
simply delete the numbers. thanks

:

Try this:

Select the range of cells to be impacted
Press the [F5] key........a shortcut for <edit><go to>
Click [Special]
Select: Formulas.....Uncheck: Text
Click [OK]

That will select all of the numeric values (and errors)
Press the [Delete] key to erase the contents of the selected cells

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

I have a column with numbers and text - I want to delete all
numerical
values
and leave the text intact. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Something different. Works on column A; change to suit and extract text to
column B.

Option Explicit
Sub sistence()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "(\D)"
End With
Set Myrange = Range("A1:A100")' Alter to suit
For Each C In Myrange
C.Select
Outstring = ""
Set Collection = RegExp.Execute(ActiveCell.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
ActiveCell.Offset(0, 1).Value = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub

Mike
 
If each cell ALWAYS contains ONLY 3 fields, separated by commas
AND
you ALWAYS want to remove the middle field....

Try this:

1)Save the workbook!
2)Select the range of cells to be impacted
3)From the Excel Main Menu:
<edit><replace>
Find what: ,*,
Replace with: (leave this field blank)
Click [Replace All]

That will change this: xxxxxxxxx, 131414265, xxxxxxxx
into this: xxxxxxxxx xxxxxxxx

If that is NOT what you want.....<edit><UNDO>

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
Yes (ideally without the double commas).

Ron Coderre said:
I need more information.....

Are you saying that one cell contains this kind of text?:
xxxxxxxxx, 131414265, xxxxxxxx

and you want to adjust it to contain (all in the same cell):
xxxxxxxxx, , xxxxxxxx

or is your situation different?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

thd3 said:
Ron - it looks like it should work but I keep getting "no cells
found" -
The
cells I am looking for look like xxxxxxxxx, 131414265, xxxxxxxx - I
want
to
simply delete the numbers. thanks

:

Try this:

Select the range of cells to be impacted
Press the [F5] key........a shortcut for <edit><go to>
Click [Special]
Select: Formulas.....Uncheck: Text
Click [OK]

That will select all of the numeric values (and errors)
Press the [Delete] key to erase the contents of the selected cells

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

I have a column with numbers and text - I want to delete all
numerical
values
and leave the text intact. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top