Formula Extract Characters at the Beginning of a String

[gonesalsa]

Hello
I need to extract the numbers at the beginning of a string. The problem is that there could 1, 2 or 3 digits. Here are examples of the string:

4JXXXXXXXXX1204
10EXXXXXXXXX1204
127JXXXXXXXXX1204

I only want to extract the 4, 10 and the 127 in the above strings. I tried =LEFT(B1,LEN(B1)-14), but the formula isn't working on the last string where the number is 3 digits. it only pulls 12, instead of 127.

Thanks

 

[billszysz]

Hi Rick Rothstein,

Good to know that someone else shares my opinion )

Regards

 

[Rick Rothstein]

Now look at my post # 9 .. I expressed the opinion that we should be careful with more than 3 digits. This means that we know something about our data (first post, examples)
But for a maximum of three digits is absolutely sufficient. (I mean this solution pgc01 =-LOOKUP (1,-LEFT (A1, {1,2,3,4})) without ";4".)

Unfortunately, that formula (modified to remove the ",4") is not foolproof (this is where the "volunteers watch each other's backs" comment I mentioned earlier comes in... to handle the "blind spots" that crop up). Try the formula with an entry like this...

1E5ABC

 

[billszysz]

Unfortunately, that formula (modified to remove the ",4") is not foolproof (this is where the "volunteers watch each other's backs" comment I mentioned earlier comes in... to handle the "blind spots" that crop up). Try the formula with an entry like this...

1E5ABC

But your example is not correct .... we have max 3 digits and few letters behind )
So we don't have 1E5ABC but maybe we have 11EABC or 1EABCD ))

Regards

 

[gonesalsa]

Thanks everyone for your help, especially to bill for trying to explain this in English.

 

[billszysz]

Thanks everyone for your help, especially to bill for trying to explain this in English.

Thanks for your feedback gonesalsa
This is correct word.... i was "trying" ))
Regards

 

[XOR LX]

Unfortunately, that formula (modified to remove the ",4") is not foolproof (this is where the "volunteers watch each other's backs" comment I mentioned earlier comes in... to handle the "blind spots" that crop up). Try the formula with an entry like this...

1E5ABC

But your example is not correct .... we have max 3 digits and few letters behind )
So we don't have 1E5ABC but maybe we have 11EABC or 1EABCD ))

Not sure I understand. Why isn't this a possible string based on the OP's original statement? Or if we replace "JX" in the 1st string below with "E7", for example? Where does it say that the "X"s can't be numbers?

I need to extract the numbers at the beginning of a string. The problem is that there could 1, 2 or 3 digits. Here are examples of the string:

4JXXXXXXXXX1204
10EXXXXXXXXX1204
127JXXXXXXXXX1204

 

[billszysz]

For me it was clear.... 4JXXXXXXXXX1204 not 4JXXXX12XXX1204.... digits only at the begining and end of the string.
Maybe I was wrong but I think that gonesalsa put those strings in this way, we can see the numbers are only at the beginning and end.
Regards

 

[gonesalsa]

yes, that's right. The numbers are only at the beginning and end.

 

[billszysz]

yes, that's right. The numbers are only at the beginning and end.

Thanks for explanation gonesalsa )

Regards

 

[Rick Rothstein]

It's slightly unfortunate that the "bulk-testing" approach with LEFT (or RIGHT) still probably makes up the majority of proposed solutions to questions of this type, despite its evident lack of rigour.

Okay then, this array-entered formula for getting the leading number from a cell should be fully rigorous...

=LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,10)),1)),0)-1)

And while we are at it, this array-entered formula for getting the ending number from a cell should also be fully rigorous...

=RIGHT(A3,MATCH(TRUE,ISERROR(1*RIGHT(A3,ROW(INDEX(A:A,1):INDEX(A:A,10)))),0)-1)