Extracting a portion of text string from a cell

**TKJ** · 05-30-2013, 11:54 AM

Help!

I need to extract the first letter 'N' followed by the numbers until the next any letter or blank in a string in a cell. EG

C5N6 ------------ N6
C5N6R1R2 ------- N6
C5N6R1 --------- N6
C5N15 ---------- N15
C5N11R1R2R3 -----N11

Can this be done using formulas and functions?

Thx

**Ace_XL** · 05-30-2013, 12:13 PM

Try

="N"&LOOKUP(99^99,--("0"&MID(RIGHT(A1,LEN(A1)-FIND("N",A1)),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(A1,LEN(A1)-FIND("N",A1))&"0123456789")),ROW($1:$10000))))

**XOR LX** · 05-30-2013, 12:26 PM

Or this array formula:

=MID(A1,FIND("N",A1),IFERROR(MATCH(FALSE,ISNUMBER(--(MID(A1,FIND("N",A1)+1,ROW(INDIRECT("1:"&(LEN(A1)-FIND("N",A1))))))),0),LEN(A1)-FIND("N",A1)))

Regards

**TKJ** · 05-30-2013, 12:35 PM

Thank you so much, it works

**Jonmo1** · 05-30-2013, 12:49 PM

If what you posted is a consistent sample of your data...

Then can we assume the string you're looking for is always :
1. Beginning at the 3rd character position
2. Followed by "R" or Nothing

If yes to both, then try

=REPLACE(LEFT(A1,FIND("R",A1&"R")-1),1,2,"")