Replace Question

  • Thread starter Thread starter Steveal
  • Start date Start date
S

Steveal

I have a long column of numerical data with occassional random
occurences of text in a cell, like so:

25
37.5
48
25.9
57
NAM
65
59
24
etc.

I want to replace every occurrence of "NAM" (always the same text)
with the contents of the cell immediately above.
In the example above I want to replace NAM with 57.

Can I do this using Find/replace?

Steve
 
Sub ChangeEm()
Dim cell As Range
Dim FirstCell As String

With Worksheets("Sheet3").Columns("A")
Set cell = .Find("NAM", LookIn:=xlValues)
If Not cell Is Nothing Then
Do
cell.Value = cell.Offset(-1, 0).Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing
End If
End With
End Sub
 
Assuming that there are no formulas in the range...

Select the range to fix (include both the numbers and text)

Edit|goto|special
Select Constants
but uncheck Numbers, logicals and errors
Leave Text checked.

This should leave you with just the text cells selected.

Type the equalsign and hit the uparrow on the keyboard.

And hit ctrl-enter to enter all the cells with a formula that retrieves the
value from the cell above.

Select the range again (both the constants and the formulas)
Edit|copy
Edit|paste special|values
 
Bob,

Works perfectly, thanks.

Dave,

Truly astonishing. I was hoping there was some "mechanical" way to do
it, and this is it!

Thanks to you both.

Steve
 
Those are interesting solutions. Another simple solution is to use the "IF"
function in a "helper column". For example, if your data is in column A
starting with cell A2, Put this formula in B2 and replicate down:

=IF(A2="NAM",A1,A2)

Column B then becomes your corrected data.
 
brilliant!

Dave Peterson said:
Assuming that there are no formulas in the range...

Select the range to fix (include both the numbers and text)

Edit|goto|special
Select Constants
but uncheck Numbers, logicals and errors
Leave Text checked.

This should leave you with just the text cells selected.

Type the equalsign and hit the uparrow on the keyboard.

And hit ctrl-enter to enter all the cells with a formula that retrieves
the
value from the cell above.

Select the range again (both the constants and the formulas)
Edit|copy
Edit|paste special|values
Click to expand...
 
Back
Top