+ Reply to Thread
Results 1 to 4 of 4

need to add/remove characters over thousands of cells

  1. #1
    Nadia
    Guest

    need to add/remove characters over thousands of cells

    I have thousands of payroll records that are imported into excel on a daily
    basis.
    The cost codes appear like this: 7002/1045.824240305
    The 7002/ needs to be removed and a . (dot) inserted five from the end
    like this: 1045.8242.40305
    Please note there are hundreds of different cost codes so I cannot simply
    copy and paste but they must all be coverted so the format is the same.
    I am desperate for suggestions.
    cheers,
    Nadia

  2. #2
    Anne Troy
    Guest

    Re: need to add/remove characters over thousands of cells

    Try this formula: =MID(A1,FIND("/",A1,1)+1,LEN(A1))
    Then use a custom format to get the dot in there.
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com



    "Nadia" <Nadia@discussions.microsoft.com> wrote in message
    news:6B4C9C71-FFCD-49F4-B984-E5576F23FCA6@microsoft.com...
    > I have thousands of payroll records that are imported into excel on a

    daily
    > basis.
    > The cost codes appear like this: 7002/1045.824240305
    > The 7002/ needs to be removed and a . (dot) inserted five from the end
    > like this: 1045.8242.40305
    > Please note there are hundreds of different cost codes so I cannot simply
    > copy and paste but they must all be coverted so the format is the same.
    > I am desperate for suggestions.
    > cheers,
    > Nadia




  3. #3
    Dave Peterson
    Guest

    Re: need to add/remove characters over thousands of cells

    Another option:

    =REPLACE(REPLACE(A1,LEN(A1)-4,,"."),1,5,"")

    If those prefix characters to be removed are always 5 characters.

    Nadia wrote:
    >
    > I have thousands of payroll records that are imported into excel on a daily
    > basis.
    > The cost codes appear like this: 7002/1045.824240305
    > The 7002/ needs to be removed and a . (dot) inserted five from the end
    > like this: 1045.8242.40305
    > Please note there are hundreds of different cost codes so I cannot simply
    > copy and paste but they must all be coverted so the format is the same.
    > I am desperate for suggestions.
    > cheers,
    > Nadia


    --

    Dave Peterson

  4. #4
    Nadia
    Guest

    Re: need to add/remove characters over thousands of cells

    Thank you both so very much for taking the time to reply.
    Dave yours worked a treat!
    cheers,
    Nadia

    "Dave Peterson" wrote:

    > Another option:
    >
    > =REPLACE(REPLACE(A1,LEN(A1)-4,,"."),1,5,"")
    >
    > If those prefix characters to be removed are always 5 characters.
    >
    > Nadia wrote:
    > >
    > > I have thousands of payroll records that are imported into excel on a daily
    > > basis.
    > > The cost codes appear like this: 7002/1045.824240305
    > > The 7002/ needs to be removed and a . (dot) inserted five from the end
    > > like this: 1045.8242.40305
    > > Please note there are hundreds of different cost codes so I cannot simply
    > > copy and paste but they must all be coverted so the format is the same.
    > > I am desperate for suggestions.
    > > cheers,
    > > Nadia

    >
    > --
    >
    > Dave Peterson
    >


+ 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