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

[timedge]

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

 

[mole999]

the apostrophe helps when any of your numbers lead with a zero

 

[BlueHornet]

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?

 

[timedge]

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

 

[BlueHornet]

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?