Deleting numerical values within a cell
 

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.

Deleting numerical values within a cell
 

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" wrote in message
...
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.

Deleting numerical values within a cell
 

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

On Oct 16, 6:47 pm, thd3 wrote:
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.

Deleting numerical values within a cell
 

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
--
Gary''s Student - gsnu200750


"thd3" wrote:

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.

Deleting numerical values within a cell
 

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

On Oct 16, 7:11 pm, vezerid wrote:
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

On Oct 16, 6:47 pm, thd3 wrote:

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.

Deleting numerical values within a cell
 

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" wrote:

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" wrote in message
...
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.

Deleting numerical values within a cell
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

Deleting numerical values within a cell
 

Yes (ideally without the double commas).

"Ron Coderre" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

Deleting numerical values within a cell
 

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

Rick

"thd3" wrote in message
...
Yes (ideally without the double commas).

"Ron Coderre" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

Deleting numerical values within a cell
 

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

"thd3" wrote:

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.

Deleting numerical values within a cell
 

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" wrote in message
...
Yes (ideally without the double commas).

"Ron Coderre" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.