Using the replace option
Using the replace option
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Using the replace option

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =REPLACE(A2,1,1,new_text)

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com