+ Reply to Thread
Results 1 to 3 of 3

Replacing a part of a number only

Hybrid View

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    rochester, ny
    MS-Off Ver
    Excel 2010
    Posts
    39

    Replacing a part of a number only

    I want to be able to copy and paste a large amount of accounting codes in a column, and have a formula in another cell which replaces only a section of the accounting code with an appropriate value.

    For example, if I enter 02-22300-1111-2222-333-4, I'd want the value in the target cell to show up as 02-44100-1111-2222-333-4. However, if I entered 02-22600-1111-2222-333-4, the target cell would become 02-40300-1111-2222-333-4. Attached is an example.

    I thought I could accomplish it by combining the if, find, and replace functions but I have not been completely successful so far.

    Any help would be appreciated.
    Attached Files Attached Files
    Last edited by mikeyt354; 03-30-2010 at 08:48 AM.

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500

    Re: Replacing a part of a number only

    Perhaps

    =IF(MID(C7,4,5)="22300",REPLACE(C7,4,5,44100),IF(MID(C7,4,5)="22600",REPLACE(C7,4,5,40300),C7))

    Does that work?

    Regards

    Dav

  3. #3
    Registered User
    Join Date
    12-17-2009
    Location
    rochester, ny
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Replacing a part of a number only

    Quote Originally Posted by Dav View Post
    Perhaps

    =IF(MID(C7,4,5)="22300",REPLACE(C7,4,5,44100),IF(MID(C7,4,5)="22600",REPLACE(C7,4,5,40300),C7))

    Does that work?

    Regards

    Dav
    It works like a charm Dav! I've never heard of the Mid function before, guess I'll have to start messing around with it. Thanks for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1