+ Reply to Thread
Results 1 to 6 of 6

substituting two text occurances in same cell.

  1. #1
    Hassan Alameh
    Guest

    substituting two text occurances in same cell.

    Hi,

    I have a long list of times entered as 8:30am - 9:15pm , where there
    is no space between the time and the text am & pm.

    Can I use the formula substitute to replace both the "am" and
    "pm" with " am" and " pm" respectively,

    i.e to insert a space between the time and text in a single formula?



    is it possible to use OR to replace BOTH of them?



    I would appreciate any help....



    Thank you.


  2. #2
    Registered User
    Join Date
    01-20-2005
    Posts
    21
    Try this formula:

    =REPLACE(A1,(LEN(A1)-1),2," "&RIGHT(A1,2))

  3. #3
    KL
    Guest

    Re: substituting two text occurances in same cell.

    Hi Hassan ,

    Try this

    =SUBSTITUTE(SUBSTITUTE(A1,"am"," am"),"pm"," pm")

    Regards,
    KL


    "Hassan Alameh" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > I have a long list of times entered as 8:30am - 9:15pm , where there
    > is no space between the time and the text am & pm.
    >
    > Can I use the formula substitute to replace both the "am" and
    > "pm" with " am" and " pm" respectively,
    >
    > i.e to insert a space between the time and text in a single formula?
    >
    >
    >
    > is it possible to use OR to replace BOTH of them?
    >
    >
    >
    > I would appreciate any help....
    >
    >
    >
    > Thank you.
    >




  4. #4
    CLR
    Guest

    Re: substituting two text occurances in same cell.

    You could also do it with Find > Replace in two steps on the whole column,
    in place, by replacing the "a" with " a" and the "p" with " p" without the
    quotes.

    Vaya con Dios,
    Chuck, CABGx3


    "KL" <[email protected]> wrote in message
    news:u5kr#[email protected]...
    > Hi Hassan ,
    >
    > Try this
    >
    > =SUBSTITUTE(SUBSTITUTE(A1,"am"," am"),"pm"," pm")
    >
    > Regards,
    > KL
    >
    >
    > "Hassan Alameh" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi,
    > >
    > > I have a long list of times entered as 8:30am - 9:15pm , where there
    > > is no space between the time and the text am & pm.
    > >
    > > Can I use the formula substitute to replace both the "am" and
    > > "pm" with " am" and " pm" respectively,
    > >
    > > i.e to insert a space between the time and text in a single formula?
    > >
    > >
    > >
    > > is it possible to use OR to replace BOTH of them?
    > >
    > >
    > >
    > > I would appreciate any help....
    > >
    > >
    > >
    > > Thank you.
    > >

    >
    >




  5. #5
    Hassan Alameh
    Guest

    Re: substituting two text occurances in same cell.

    It worked perfectly, Thanks alot dear KL,
    i'm so grateful


  6. #6
    Hassan Alameh
    Guest

    Re: substituting two text occurances in same cell.

    , missed that !!! thank u clr


+ 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