Results 1 to 5 of 5

Thread: How to remove the apostrophe in front of a text, then find a two digit code in the text
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to remove the apostrophe in front of a text, then find a two digit code in the text

    This is a two part question. Have a report with an apostrophe in front of a series of numbers. The first part is to remove the apostrophethen the second part is to find a specific two digit number within the seriesof numbers. Reading the series right to left in two digit format. There is a littleproblem some maybe blanks and other may have an asterisk in the middle of thestring, then follow by addition numbers. Only need to read the number in front of the asterisk.


    Letís say I need to find the number 25 in the below examples

    Ď21125225

    Ď21125225*****13666
    Thanks in Advance

    timedge


  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to remove the apostrophe in front of a text, then find a two digit code in the text

    the apostrophe helps when any of your numbers lead with a zero
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Apr 2012
    Location
    Connecticut
    Posts
    332
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove the apostrophe in front of a text, then find a two digit code in the text

    Welcome to the board.

    Some clarifications are in order, I think. First of all, you had mentioned "reading right to left", which I wanted to bring to your attention in case that's mistaken. In the language in which we're conversing it's pretty universal to read from left-to-right, and not from right to left, as you had described.

    Second, "find the number 25" is problematic, since the string "2-5" occurs twice in each string (reading left-to-right) and also occurs once in each string (reading right-to-left).

    As mole999 has started to explain, the apostrophe exists in the cells to identify these values as string values, and not as numbers which could be used in calculation. (As 'numbers', for example, your second string would be an error value.)

    So after the clarification the question relates to the regularity of your strings. It's not difficult at all to do a =MID( ) function on your string values to extract a substring from your string values, but the question is whether you always want to extract "some substring" at a particular location, or does the location vary with the length of the string? Or does it vary based on something else?
    Last edited by BlueHornet; May 6th, 2019 at 12:10 PM.

  4. #4
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove the apostrophe in front of a text, then find a two digit code in the text

    Sorry for the mistake the reading is left to right. The string could be empty “blank”, if data isin the cell, it could be in two format one with asterisk or without asteriskwith no set length on both format. But both will havethe apostrophe. Not sure whatyou mean about substring?


    timedge


  5. #5
    Board Regular
    Join Date
    Apr 2012
    Location
    Connecticut
    Posts
    332
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove the apostrophe in front of a text, then find a two digit code in the text

    A substring is any part of a string. (That's not just Excel, that's kind of a "Introduction to Computers 101" thing.)

    It's not clear whether you're looking for the "25" substring in the entries that you have (or counting them, when they appear more than once in a string), or what. Are you looking to see what the substring values at specific locations in the string?

    What, exactly, are you looking for?

Some videos you may like

User Tag List

Tags for this Thread

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
  •