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

timedge

New Member
Joined
Dec 4, 2018
Messages
9
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

 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
the apostrophe helps when any of your numbers lead with a zero
 
Upvote 0
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:
Upvote 0
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

 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top