Formula Finding String with Specific Letters and Numbers

**jpedges38** · 07-06-2012, 10:20 AM

Say I have these two lines in Column A:

3051S2TG4A2A11X5AWA3WK1I5A1140
3051S2LD2AA1A1030DFF71CA00

I need to be able to have a formula in Column B that Finds the letter "A" when it is followed by four numbers, and then return "A and the 4 numbers."

So for the first line, the result in Column B would be "A1140," and the second line result would be "A1030"

Any help would be much appreciated.

Thanks,
J

**jason.b75** · 07-06-2012, 05:14 PM

Array formula, must be confirmed with Shift Ctrl Enter

=MID(A2,MATCH(1,(MID(A2,ROW($A1:$A255),1)="A")*ISNUMBER(--MID(A2,ROW($A2:$A256),4)),0),5)

**JBeaucaire** · 07-06-2012, 06:09 PM

Great formula, Jason.

Here's a recommended adjustment to reduce the number of calculations per cell from 500 to just number of letters per cell.

=MID(A2,MATCH(1,(MID(A2,ROW(INDIRECT("$1:$" & LEN(A2)-4)),1)="A")*ISNUMBER(--MID(A2,ROW(INDIRECT("$2:$" & LEN(A2)-3)),4)),0),5)

**jason.b75** · 07-06-2012, 06:31 PM

I thought about that Jerry, but depending on the number of records it can be false economy.

I've tweaked the formula to reduce the number of precedent cells, something I've tried to get into the habit of is avoiding volatile functions unless it is known that the record count is fairly low.

Maybe that's just the result of being over-exposed to the suggestions of a certain over-confident MVP in another forum.

**shg** · 07-06-2012, 07:29 PM

The problem with not using INDIRECT in that formula is that it will change if you insert rows above row 255. That wouldn't cause a problem here, but inserting rows above row 1 would.

**JBeaucaire** · 07-06-2012, 08:56 PM

Lots of good warnings with each approach.

I would guess the real benefit is to use whatever formula works, then use a COPY>PASTESPECIAL>VALUES to remove the formulas and leave the extracted code behind. Makes either method safe to employ short term.

@JPedges38 - If these take care of your need, please select Thread Tools from menu above and set this topic to SOLVED.