Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric String

Dear all,

I'm trying to extract numbers from an Alpha-Numeric String.

I need to extract only the 1st 'n' digits from the Alpha-Numeric string where 1<n<=8.

Input Output Scenarios
ab1245678ab 1245678 Extracted 1st 8 digits [n=8]
ab124cd 124 Extracted 1st 3 digits (3<=8) [n=3]
ab12456789cd 1245678 Extracted 1st 8 digits only. Hence 9 is not present in the output [n=8]
abcd1234acdf12 1234 Extracted 1st 4 digits (4<=8) [n=4]

PFA for the sheet explaining the Condition and Scenarios

I do have a formula to extract numbers but I cannot restrict it to extract upto 1st 'n' digits:

Please Login or Register  to view this content.

Note1: If your input is in A1 then type your formula in B1. After you have written formula, you have to Press CTRL+SHIFT+Enter

Note2:This formula doesn't work for the last scenario mentioned above.

Also, keep in mind that I would work with dynamic Range of data.

I would like to achieve this through a macro if this would be efficient than writing formulas.

Any help would be much appreciated.

Thanks,
Vinod Krishna

Try this regular formula, copied down:

Please Login or Register  to view this content.

Is that something you can work with?

Try this :

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

@Vinod Krishna,

you expected:

ab12456789cd ==> 1245678

but Ron's ==>12456789

Does it meet your requirement?

Originally Posted by bebo021999

Try this :

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Dear Bebo,

Your formula works brilliantly

Also, looks simpler when compared to Ron's Solution. I hope this works even when the data grows for 10000+ rows

Could you please explain the formula?

"ab12456789cd 1245678 Extracted 1st 8 digits only. Hence 9 is not present in the output [n=8]"

But surely this is just a counting error?

And where is it said that the number 9 should be categorically excluded from all strings? Saying you want the "first 8 digits" is not the same as saying that the number 9 shoud be excluded.

Ron's solution seems perfectly in accordance with the initial description as I see it.

Regards

@Bebo021999: We each made different assumptions. I guessed that the OP wanted to limit the length of the returned value. You guessed that the OP wanted to exclude specific digits. I still don't know which of us got it right.

Originally Posted by Ron Coderre

@Bebo021999: We each made different assumptions. I guessed that the OP wanted to limit the length of the returned value. You guessed that the OP wanted to exclude specific digits. I still don't know which of us got it right.

Surely that your formula is the best with error trap, in case limit of length of return value is 8. At first time I were confused with

'n' digits from the Alpha-Numeric string where 1<n<=8.

but

ab12456789cd 1245678

Maybe OP got typo.

Anyway, both of us are "brilliant" , although we don't know which solution is chosen.

Dear all,

I totally forgot to include the other scenarios

Need your help to modify your formulas for the following scenarios:

Input: ab00000123456789cd
Output: 00000123 [Extracted 1st 8 Digits including 0's]

Input : ab012345678cd123
Output: 01234567 [Extracted 1st 8 digits]

Input : ab0cde
Output : 0 [Extracted the digit 0]

Logic to be applied : Irrespective ** the numbers in the alpha-numeric string, we need to extract 1st 'n digits where 1<n<=8

Many thanks.

Regards,
Vinod Krishna

Does Ron' formula in #2 work?

Hi Vinod Krishna

I think the best solution for your problem is to use VB UDF.

Here is what you need to do:

1. Press ALT key and F11 to open VB Editor.
2. Go to Insert and click on Module
3. Copy and paste code below into module and close VB Editor.

In your spreadsheet type formula

=LEFT(KillText(A1),8)

	A	B
1	ab00000123456789cd	00000123
2	ab012345678cd123	01234567
3	ab0cde	0

Code to use:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.