Excel: Extracting numbers from a string with variable length

Asked Dec 10 '21 at 17:26

Active Dec 10 '21 at 17:26

Viewed 242 times

I have a column with data that looks like this:

Day
D003
D004
D008
D010
D012
D028

And in the next column, I need to extract just the 3, 4, 10, 12, etc. with NO leading 0's. I tried the following:

=IF(D8="D003 Predose","30",IF(D8="D003 End","31",IF(D8="D003 4hours","34",RIGHT(D8,LEN(D8)-FIND(RIGHT(SUBSTITUTE(D8,"0",""),1),D8)+1))))

Which works for the 4, 5, 8. But for the 12, 17, 21, etc it only gives me the last number. How do I account for the variable length of those strings? I'm really new at Excel and creating formulas "from scratch" so I apologize if the answer is right in front of my nose. If there's a cleaner way to do this, I'm also all ears.

asked Dec 10 '21 at 17:26

RaleighWalker

Will there only ever be one letter in the string ("D")? – cybernetic.nomad Dec 10 '21 at 17:28
Yes, it will only ever be D### – RaleighWalker Dec 10 '21 at 17:29
1

`=--MID(A1,2,3)` should work. – JvdV Dec 10 '21 at 17:30
=--MID(A1,2,3) works, I had NO idea you could just add the -- to the front. I see the result works, but just so I understand, exactly what is it doing? How does it "know" to remove the leading 0's from the different lengths of the digits? I.e., 003 = 3 and 012 = 12 – RaleighWalker Dec 10 '21 at 17:34
Rouch explanation: essentially, the `--` ends up convert the string to a number. Unless your cells are formatted to include leading zeroes, you won't see them. – cybernetic.nomad Dec 10 '21 at 18:12
Extremely helpful. Thanks all. – RaleighWalker Dec 10 '21 at 18:51

Excel: Extracting numbers from a string with variable length

0 Answers0