Deleting numerical values within a cell

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

 

[Ron Coderre]

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)

 

[vezerid]

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

 

[Guest]

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

 

[vezerid]

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

 

[Guest]

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.

 

[Ron Coderre]

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)

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.

 

[Guest]

Yes (ideally without the double commas).

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)

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.

 

[Rick Rothstein \(MVP - VB\)]

Are your numbers always surrounded by commas like you showed? Can there be
more than one number in your text?

Rick

Yes (ideally without the double commas).

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)

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.

 

[Guest]

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

 

[Ron Coderre]

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)

Yes (ideally without the double commas).

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)

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.