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

 

[Rick Rothstein]

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)

Since I set these up to return text values, I probably should have provided for more digits than a max of 10 of them... let's go for up to 99 digits then,

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

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

Because these are array-entered** formulas, I should have also provided the standard blurb for future readers of this thread..

**Commit the above formulas using CTRL+SHIFT+ENTER and not just Enter by itself

 

[nithin shettigar]

try this =-LOOKUP(1,-LEFT(A1,{1,2,3,4,5,6,7,8,9,10}))

 

[Rick Rothstein]

try this =-LOOKUP(1,-LEFT(A1,{1,2,3,4,5,6,7,8,9,10}))

You did not read the entire thread... try that formula against either of these values...

2E5ABV4E5

<tbody>
</tbody>

2SEP14ABC915

<tbody>
</tbody>

That is why I emphasize the words "fully rigorous" in my latest postings (Messages #30 and #31).

 

[XOR LX]

You did not read the entire thread... try that formula against either of these values...

2E5ABV4E5

<TBODY>
</TBODY>

2SEP14ABC915

<TBODY>
</TBODY>

That is why I emphasize the words "fully rigorous" in my latest postings (Messages #30 and #31).

Quite.

But this is my point: this solution has become almost ubiquitous for questions of this type around the various forums on the internet. And the more widely-proposed (and unknowingly accepted) it becomes, the more I imagine it gets pushed to the top of any Google searches for e.g. "Excel find first group of numbers in a string".

We have a long quest ahead of us...

 

[XOR LX]

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

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

The first looks pretty rigorous to me. Like the idea of using INDEX for the ROW here in place of INDIRECT, by the way.

Not sure about the second, though, as it's still using the "bulk" approach to a certain extent: what about a string such as ABC-1234?

Regards

 

[XOR LX]

If you want to use an approach "from the right", it's probably safer to tweak an approach using MID: at least then we guarantee that we are always inspecting "individual" characters, e.g.:

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

or use LOOKUP:

=RIGHT(A3,LOOKUP(2,1/(ISERROR(1*MID(A3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A3))),1))),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A3)))))

which no longer requires array-entry.

Regards

 

[Rick Rothstein]

If you want to use an approach "from the right", it's probably safer to tweak an approach using MID: at least then we guarantee that we are always inspecting "individual" characters, e.g.:

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

Do you really think it necessary to do that? Can you cite a text string that would require it (I cannot think of one)?

 

[XOR LX]

Do you really think it necessary to do that? Can you cite a text string that would require it (I cannot think of one)?

I gave one in post #35.

Regards

 

[Rick Rothstein]

Do you really think it necessary to do that? Can you cite a text string that would require it (I cannot think of one)?

I gave one in post #35.

I don't recall seeing that when I first read your message... was that something you edited in later on?

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

The first looks pretty rigorous to me. Like the idea of using INDEX for the ROW here in place of INDIRECT, by the way.

Not sure about the second, though, as it's still using the "bulk" approach to a certain extent: what about a string such as ABC-1234?

An argument could be made that -1234 is the trailing number, but I take your point. This, of course, brings up another point... the need to know what the formula is supposed to return. For example, given this text string...

ABC12.3

what is the trailing number... 3 like your array-entered trailing number replacement formula returns or 12.3 like my original trailing number formula returns? Obviously, the answer depends on what user requires from the data, so I guess that means there is no single best formula.... it looks like I'll just have to archive both.:wink: Thanks for kicking off a most interesting discussion... much appreciated.

 

[XOR LX]

An argument could be made that -1234 is the trailing number, but I take your point. This, of course, brings up another point... the need to know what the formula is supposed to return. For example, given this text string...

ABC12.3

what is the trailing number... 3 like your array-entered trailing number replacement formula returns or 12.3 like my original trailing number formula returns? Obviously, the answer depends on what user requires from the data, so I guess that means there is no single best formula.... it looks like I'll just have to archive both.:wink: Thanks for kicking off a most interesting discussion... much appreciated.

All good points.

And the discussion continues...just in another thread!

http://www.mrexcel.com/forum/excel-questions/780251-left-mid-right-variable-lenght-3.html

Regards