Using the replace option

Slick

New Member
Joined
Mar 13, 2002
Messages
5
Does anyone know how I can use the replace function to replace only one number in list of numbers. For example: 7201, 7202, 7203, 7207. I only want to replace the first number and not all the 7's within the number. Please email at Rick_Scott@SBA.com if you have any suggestions.

Thanks,
Rick
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try using the following formula:

=SUBSTITUTE(a1,oldtext,newtext,1)

Where a1 is the cell to be changed, old text is the text to be replaced by new text and 1 is the number of instances to change.

I hope this helps.
Kind regards, Al.
 
Upvote 0
If all your numbers are formatted like that, you could use text to columns. Make sure there's a blank column next to your data, select your data, click on Data-Text to Columns and select the Fixed-Width option and click Next. Move the break line past your 7 and click on finish. This will split your data into two columns, one containing the first number and the other your last 3 numbers. Then just delete your column of 7's.
 
Upvote 0
The formula =substitute(a1,oldtext,newtext,1)worked in changing my first number. However when I record my macro it will not let cover an entire range, for example the entire column B where the new number would go. Any suggestions.

Thanks,
Rick
 
Upvote 0
Hi

This code assumes your numbers that need replacing are in Column A, and puts the new numbers in column B.

Sub Replace7With8()
With Range("B1", Range("A65536").End(xlUp).Offset(0, 1))
.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""7"",""8"")*1"
.Value = .Value
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top