Extracting Characters from a cell before the first letter after the first series of number

**mbrandt5** · 05-11-2015, 09:54 AM

In cell A1 I have two variables...E1L1, EJ10L10(for example)

I would like in cell B1 the information before the character after the first series of numbers...These characters can vary in length

So If A1 equals E1L1...then B1 equals E1

If A1 equals EJ10L10...then B1 equals EJ10

Seeing that the string of characters can vary a simple character from beginning extraction would not be suitable

I'd prefer to do it with just a formula, but if a macro must be incorporated in the formula...That'd be acceptable

**Glenn Kennedy** · 05-11-2015, 10:00 AM

In both cases the first character to be "thrown out" was an L. is that always the case?

**mbrandt5** · 05-11-2015, 10:05 AM

Unfortunately no

**AlKey** · 05-11-2015, 10:13 AM

Maybe something like this

=IF(LEN(A1)<7,MID(A1,1,2),MID(A1,1,4))

Row\Col	A	B
1	E1L1	E1
2	EJ10L10	EJ10

**XOR LX** · 05-11-2015, 10:16 AM

Hi.

The fact that you gave only two examples is perhaps not enough to be sure, but maybe:

=LEFT(A1,MATCH(3,MMULT(N(ISERR(0+MID(MID(A1&"ζ",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),2),{1,2},1))),{1;3}),0))

Regards

**Glenn Kennedy** · 05-11-2015, 10:24 AM

I hope that you don't have too many to do. This works, but it's a tad slow!!!

**Glenn Kennedy** · 05-11-2015, 10:25 AM

I should have added that these are Array Formulas. They're a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Extracting Characters from a cell before the first letter after the first series of number

LinkBack

Thread Tools

Rate This Thread

Display

Extracting Characters from a cell before the first letter after the first series of number

Re: Extracting Characters from a cell before the first letter after the first series of nu

Re: Extracting Characters from a cell before the first letter after the first series of nu

Re: Extracting Characters from a cell before the first letter after the first series of nu

Re: Extracting Characters from a cell before the first letter after the first series of nu

Re: Extracting Characters from a cell before the first letter after the first series of nu

Re: Extracting Characters from a cell before the first letter after the first series of nu

Thread Information

Users Browsing this Thread

Similar Threads

extracting only letter characters

[SOLVED] Extracting Number of Characters

Extracting a number (part of a series of numbers) from a text string

deleting all characters after the first letter in a cell and repeatign for entire row

finding characters after a letter in cell

Bookmarks

Bookmarks

Posting Permissions