checking if a cell contains text and deleting it

[jaycee]

I have a column (D) that contains letters and numbers ie e6788764, But if this row contains the letters "D", "F", "K" or "S" then I need to delete those letters but keep the numbers.
The length of the numbers also differ.

Any ideas how this can be done?

 

[mikerickson]

This will do the job

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1, "D", ""),"F", ""),"K", ""),"S", "")

 

[jaycee]

Thanks for the quick reply but where do i put this code?

 

[Steeviee]

Not sure if there is loads of data, if so you might want to replace the offending cells?

Anyway, example of simplified bit of code:

Sub try()
x = 2
Do Until x = 10
If Left(Cells(x, 4), 1) = "d" Then
Cells(x, 4) = Right(Cells(x, 4), Len(Cells(x, 4)) - 1)
End If
x = x + 1
Loop
End Sub

 

[mikerickson]

That fromula goes in row 1 of a helper column,
is dragged down to the end of the data,
and (perhaps) copy/Paste Special Values the result into Column D.

 

[jaycee]

I tried to put it in row "D" but this then deletes when I put the data in.

I have also tried in a seperate column, but this only copies what was in the original row "D" admittedly with those letters deleted, but now in the wrong column?

 

[Jon von der Heyden]

Highlight the range with the values in column D.

Hit CONTROL+H (Find & Replace)
Find: E
Replace: <leave this blank>

Hit Replace All.

Repeat for other letters in the column that you wish to remove.