Extracting a number in between characters

[Capri]

I have text in this format:

Filename , Pages:[1-4 digit number]/Rows:[1-4 digit number], Format, From: Name

The text is in A1 and I need to extract the 1-4 digit number in between the first colon and slash into B1 and the 1-4 digit number in between the slash and second colon into C1.

Any help would be highly appreciated. Thank you.

 

[Rick Rothstein]

Thank you for this, Rick.

How would I add a formula to ignore the cell (i.e. A1) if the cell were blank? For example, if A1 is blank, I get a #VALUE! in B1.

Like this...

=IFERROR(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"/",":"),",",":"),":",REPT(" ",200)),2*COLUMNS($B:B)*200,200)),"")

 

[excelliot]

if your text in cell A1 is like

Filename , Pages:1000/Rows:2000, Format, From: Name

then use

1st Formula

=LOOKUP(10^17,LEFT(MID(A1,FIND(":",A1)+1,LEN(A1)),COLUMN($1:$1))*1)

this will give result 1000
2nd Formula

=LOOKUP(10^17,LEFT(MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,LEN(A1)),COLUMN($1:$1))*1)

this will give result 2000

Cheers!!

 

[Rick Rothstein]

1st Formula

=LOOKUP(10^17,LEFT(MID(A1,FIND(":",A1)+1,LEN(A1)),COLUMN($1:$1))*1)

this will give result 1000
2nd Formula

=LOOKUP(10^17,LEFT(MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,LEN(A1)),COLUMN($1:$1))*1)

this will give result 2000

I posted my original formula in Message #6 and the OP wrote this back to me in Message #10...

"How would I add a formula to ignore the cell (i.e. A1) if the cell
were blank? For example, if A1 is blank, I get a #VALUE! in B1."

So I posted my correction to handle it in Message #11... while your formulas do not produce a #VALUE! error if A1 is blank, they do produce a #N/A error which I am sure the OP also does not want to see, so you should modify them to eliminate that error for blank cells.

 

[Capri]

Like this...

=IFERROR(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"/",":"),",",":"),":",REPT(" ",200)),2*COLUMNS($B:B)*200,200)),"")

Thank you for the formula, Rick.

However, I got a #Name? error. What did I do wrong? I replaced $A1 with the actual cell that had the text, i.e. $G1

 

[Capri]

if your text in cell A1 is like

Filename , Pages:1000/Rows:2000, Format, From: Name

then use

1st Formula

=LOOKUP(10^17,LEFT(MID(A1,FIND(":",A1)+1,LEN(A1)),COLUMN($1:$1))*1)

this will give result 1000
2nd Formula

=LOOKUP(10^17,LEFT(MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,LEN(A1)),COLUMN($1:$1))*1)

this will give result 2000

Cheers!!

Thank you Excelliot. This formula worked as well. As Rick mentioned, I did get a #N/A error for the blank cells.

 

[Rick Rothstein]

Thank you for the formula, Rick.

However, I got a #Name? error. What did I do wrong? I replaced $A1 with the actual cell that had the text, i.e. $G1

Are you using XL2003 or earlier? If so, ISERROR did not exist in those versions.

 

[Capri]

Are you using XL2003 or earlier? If so, ISERROR did not exist in those versions.

I'm using 2003.

 

[Rick Rothstein]

I'm using 2003.

You should mention that fact whenever you ask a question in a forum so the volunteers will know not to use any of the newer stuff introduced in later versions. This formula should work for you in your version of Excel...

=IF(ISNUMBER(FIND(":",$A1)),0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"/",":"),",",":"),":",REPT(" ",200)),2*COLUMNS($B:B)*200,200)),"")

 

[Capri]

You should mention that fact whenever you ask a question in a forum so the volunteers will know not to use any of the newer stuff introduced in later versions. This formula should work for you in your version of Excel...

=IF(ISNUMBER(FIND(":",$A1)),0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"/",":"),",",":"),":",REPT(" ",200)),2*COLUMNS($B:B)*200,200)),"")

Rick,

Thank you for the advice. Will keep that in mind.

 

[excelliot]

yes, you can add iferror condition before formula to avoid the same..

Cheers!!