1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Find/Replace

    From Saxman@21:1/5 to All on Fri Jan 24 21:02:19 2020
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

     Find What:  -
     Replace With:  /


    I then end up with two columns of data and no /.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From GS@21:1/5 to All on Fri Jan 24 15:25:18 2020
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

    Find What: -
    Replace With: /

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Zaidy036@21:1/5 to Saxman on Sat Jan 25 16:30:32 2020
    Saxman <john.h.williams2removethis@gmail.com> wrote:
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

     Find What:  -
     Replace With:  /


    I then end up with two columns of data and no /.


    Concat(',.....,/,...)

    --
    Zaidy036

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From GS@21:1/5 to All on Sat Jan 25 11:45:50 2020
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

     Find What:  -
     Replace With:  /


    I then end up with two columns of data and no /.

    I selected several cells containing dates as Text, formatted mm-dd-yyyy-00; After doing Replace above, the Text was changed to mm/dd/yyyy/00!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Saxman@21:1/5 to All on Mon Jan 27 12:54:07 2020
    On 25/01/2020 16:30, Zaidy036 wrote:
    Saxman <john.h.williams2removethis@gmail.com> wrote:
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

     Find What:  -
     Replace With:  /


    I then end up with two columns of data and no /.


    Concat(',.....,/,...)



    Under what Category?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Zaidy036@21:1/5 to Saxman on Mon Jan 27 11:52:03 2020
    On 1/27/2020 7:54 AM, Saxman wrote:
    On 25/01/2020 16:30, Zaidy036 wrote:
    Saxman <john.h.williams2removethis@gmail.com> wrote:
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

      Find What:  -
      Replace With:  /


    I then end up with two columns of data and no /.


    Concat(',.....,/,...)



    Under what Category?

    TEXT

    --
    Zaidy036

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Saxman@21:1/5 to All on Mon Jan 27 19:55:41 2020
    On 27/01/2020 16:52, Zaidy036 wrote:
    On 1/27/2020 7:54 AM, Saxman wrote:
    On 25/01/2020 16:30, Zaidy036 wrote:
    Saxman <john.h.williams2removethis@gmail.com> wrote:
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

      Find What:  -
      Replace With:  /


    I then end up with two columns of data and no /.


    Concat(',.....,/,...)



    Under what Category?

    TEXT


    Selecting TEXT does not give me the opportunity to change anything?

    Maybe you could explain further or provide an example.

    TIA

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Zaidy036@21:1/5 to Saxman on Mon Jan 27 17:05:36 2020
    On 1/27/2020 2:55 PM, Saxman wrote:
    On 27/01/2020 16:52, Zaidy036 wrote:
    On 1/27/2020 7:54 AM, Saxman wrote:
    On 25/01/2020 16:30, Zaidy036 wrote:
    Saxman <john.h.williams2removethis@gmail.com> wrote:
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

      Find What:  -
      Replace With:  /


    I then end up with two columns of data and no /.


    Concat(',.....,/,...)



    Under what Category?

    TEXT


    Selecting TEXT does not give me the opportunity to change anything?

    Maybe you could explain further or provide an example.

    TIA

    Answer does depend on if you want your sheet to do everything
    automatically without manual intervention or if you are willing to use functions that require operator action.

    - Assuming automatically
    Data in cell A5 is '5-2 or 5-2 displayed text
    In B5 =FIND("-",A5) =2
    In C5 =REPLACE(A5,B5,1,"/") will = 5/2 DISPLAYED as text

    Hide columns do not want to see.
    --
    Zaidy036

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Saxman@21:1/5 to All on Tue Jan 28 09:57:34 2020
    On 27/01/2020 22:05, Zaidy036 wrote:
    On 1/27/2020 2:55 PM, Saxman wrote:
    On 27/01/2020 16:52, Zaidy036 wrote:
    On 1/27/2020 7:54 AM, Saxman wrote:
    On 25/01/2020 16:30, Zaidy036 wrote:
    Saxman <john.h.williams2removethis@gmail.com> wrote:
    On 24/01/2020 20:25, GS wrote:
    I need to find and replce similar from

    5-2
    4-1
    11-1

    to

    5/2
    4/1
    11/1

    I end up with dates even though I format the cells to text.

    How can I overcome this?

    TIA

    Try just replacing the delimiter, perhaps?

      Find What:  -
      Replace With:  /


    I then end up with two columns of data and no /.


    Concat(',.....,/,...)



    Under what Category?

    TEXT


    Selecting TEXT does not give me the opportunity to change anything?

    Maybe you could explain further or provide an example.

    TIA

    Answer does depend on if you want your sheet to do everything
    automatically without manual intervention or if you are willing to use functions that require operator action.

    - Assuming automatically
    Data in cell A5 is '5-2 or 5-2 displayed text
    In B5 =FIND("-",A5)      =2
    In C5 =REPLACE(A5,B5,1,"/") will = 5/2 DISPLAYED as text

    Hide columns do not want to see.


    Works fineonce I got the formatting correct.

    Thank you.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)