Replace one number in a sequence in a group of cells

[penquicw]

I have a group of cells that start with 2 (201, 202, 203 etc.) and I need to
replace that with a 3 (301, 302, 303, etc). Is there a way in "find/replace"
to do this.? I don't know "macros" at all and formulas seem more complex
then just changing the first number in a sequence. Anyone know an easy way?

 

[NPell]

I have a group of cells that start with 2 (201, 202, 203 etc.) and I need to
replace that with a 3 (301, 302, 303, etc).  Is there a way in "find/replace"
to do this.?  I don't know "macros" at all and formulas seem more complex
then just changing the first number in a sequence.  Anyone know an easy way?

Assuming A1= 201 , A2= 202, and A3= 203..
then in B1 put =REPLACE(A1,1,1,3)
Then drag the formula down.

 

[Mike H]

Probably the easiest is to to the number 100 in a cell and copy it then
select your range of cells and then

Edit|Paste Special
Select Add
Click OK
Delete the cell with 100 in it

Mike

 

[penquicw]

That gave me the values I wanted, but I want them in the "A" column not the
"B". How do I get tehm into the "A" column and not have the "B" column.

 

[Peo Sjoblom]

Copy and paste special as values?


--


Regards,


Peo Sjoblom

 

[David Biddulph]

Copy column B,
Select column A,
Edit/ Paste Special/ Values

[As always, safest to save a copy of the original version first.]

 

[Rick Rothstein \(MVP - VB\)]

If you want a macro...

Sub ThreeForTwoSwap()
Dim C As Range
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For Each C In Range("A1:A" & LastRow)
If Left(C.Value, 1) = "2" Then C.Value = "3" & Mid(C.Value, 2)
Next
End Sub

Rick

 

[penquicw]

I figured there was a way to make a formula a value. Thank you
everyone...That worked out great.

Copy column B,
Select column A,
Edit/ Paste Special/ Values

[As always, safest to save a copy of the original version first.]
--
David Biddulph

That gave me the values I wanted, but I want them in the "A" column not
the
"B". How do I get tehm into the "A" column and not have the "B" column.