Extract text to the right of a variable string?

[Bryanf939]

Hello everyone!

I use this forum extensively to get me through my Excel problems, but I haven't yet been able to find a solution for the following problem.

I have a bunch of text in column A1

FCL289AJDA
FCL3LASD
PEL383LLD

I am trying to extract the first part of the text and subsequent numbers only. I do not want the second part of the text to be outputted.

So for the above the output should be
FCL289
FCL3
PEL383

How can I do this in Excel? I tried using LEFT, RIGHT, FIND formulas, but I was never able to solve the issue if the length of the string differed.

 

[James006]

Hello,

You can test following array formula:

=LEFT(A2,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT("1:"&LEN(A2)))),0)))

Hope this will help

 

[Rick Rothstein]

Here is an array-entered** formula that should work...

=LEFT(A1,MAX(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)),ROW(INDIRECT("1:"&LEN(A1)-1)))))

**Commit this formula using CTRL+SHIFT+ENTER and not Enter by itself

 

[XOR LX]

Another array formula**:

=LEFT(A2,MATCH(1,1/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

Regards

 

[Tom.Jones]

Hello,

You can test following array formula:

=LEFT(A2,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT("1:"&LEN(A2)))),0)))

Hope this will help

James,

Your formula fail for ABC1221CDSE

 

[Tom.Jones]

Another array formula**:

=LEFT(A2,MATCH(1,1/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

Regards

XOR LX,

Your formula fail for AQWE12JUKI

 

[XOR LX]

XOR LX,

Your formula fail for AQWE12JUKI

Thanks. Got careless with the choice of numerator. Should be:

=LEFT(A2,MATCH(1,0/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

Regards

 

[James006]

James,

Your formula fail for ABC1221CDSE

Have you actually tested it ...???

 

[RoryA]

James,

Your formula fail for ABC1221CDSE

Works fine for me as long as you array enter it as directed. Otherwise it will return ABC1.

 

[James006]

Works fine for me as long as you array enter it as directed. Otherwise it will return ABC1.

Thanks Rory for the confirmation ... and for the support ...