Using the replace option

Slick · Mar 14th, 2002, 02:10 PM

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

Al Chara · Mar 14th, 2002, 02:15 PM

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.

Mudface · Mar 14th, 2002, 02:18 PM

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.

Mark W. · Mar 14th, 2002, 02:18 PM

=REPLACE(A2,1,1,new_text)

Slick · Mar 15th, 2002, 07:55 AM

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

Dave Hawley · Mar 15th, 2002, 08:06 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 02:10 PM	#1
Slick New Member Join Date: Mar 2002 Posts: 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

Mar 14th, 2002, 02:15 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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.

Mar 14th, 2002, 02:18 PM	#3
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Mar 14th, 2002, 02:18 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=REPLACE(A2,1,1,new_text)

Mar 15th, 2002, 07:55 AM	#5
Slick New Member Join Date: Mar 2002 Posts: 5	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

Mar 15th, 2002, 08:06 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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