Extracting a certain portion of a cell in Excel 1

[frantzx]

I need some help. I am trying to extract a part of a cell with a formula. See example below:


in column A1 my data is

P-1-B103A4
P-1-B103A5
P-1-B107A10
P-1-B107A12
P-1-B10A7
P-1-B10A8
P-1-B10A9
P-1-B10B12


in column B1 I will need just the numbers that will come after P-1-B:

103
103
107
107
10
10
10
10

The ending result would be:

A1 B1
P-1-B103A4 103
P-1-B103A5 103
P-1-B107A10 107
P-1-B107A12 107
P-1-B10A7 10
P-1-B10A8 10
P-1-B10A9 10
P-1-B10B12 10


All I want from my data is the numbers between the P-1-B and the next alpha character. I also want to be able to paste new data in A1 and have the formula automatically take the portion I dont need out. I dont know if this can be done but I thought I would give it a try. Any help would be greatly appreciated.

Thanks

Brian

 

[bluedragon2]

Use the array formula:

=LEFT(MID(A2,6,LEN(A2)-5),MATCH(1,(CODE(MID(MID(A2,6,LEN(A2)-5),ROW($A$1:$A$10),1))<90)*(CODE(MID(MID(A2,6,LEN(A2)-5),ROW($A$1:$A$10),1))>=65),FALSE)-1)

Enter formula using ctrl-shift-enter

Once entered like that, You can copy down.

This also assumes that the number of characters to the right of P-1-B are 10 or less. You can increase that by changing the $A10's in the formula to a greater number.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

Thank you, Blue, for finding that. I was trying to work out the array formula to find the first non-numeric character and was about to sprain my brain.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[frantzx]

Sweet!!!!! Thank you so much!

 

[SkipVought]

Brain Sprain?

Must be using Nogain.

Try Mogain, along with your Rogain.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

>along with your Rogain

Hey, now!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Oh, I thot you'd associate a winning sculling competition, John.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}