Formula that will extract the first 4 or 5 digits from a cell.

[canadian86]

I need a formula that will extract the first 4 or 5 digits from a cell.

For example:

Store 2321 - Toronto 12-20-2010
Store # 72833- Calgary 12-20-2011
Store 02931 8A - Oakville 04-20-2011

The formula would return:

2321
72833
02931

That would help me a lot.

Thanks.

 

[Ron Coderre]

With sample text in cell A1

Try this...

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

Example
with A1: Store 02931 8A - Oakville 04-20-2011
the formula returns: 02931


Is that something you can work with?

 

[canadian86]

That seems to be working.. but instead of giving me:

"02931"

it's giving me

"02931 "

Is there a way to remove the extra space at the end?

Thanks a lot!!

 

[canadian86]

Got it.. I can use the TRIM function... thanks a lot for ur help! It works great.

 

[Scott Huish]

Perhaps:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(MID(A1,6,LEN(A1)),"#","")),"-"," ")," ",REPT(" ",254)),255))

Excel Workbook
	A	B
1	Store 2321 - Toronto 12-20-2010	2321
2	Store # 72833- Calgary 12-20-2011	72833
3	Store 02931 8A - Oakville 04-20-2011	02931
Sheet1

 

[T. Valko]

I need a formula that will extract the first 4 or 5 digits from a cell.

For example:

Store 2321 - Toronto 12-20-2010
Store # 72833- Calgary 12-20-2011
Store 02931 8A - Oakville 04-20-2011

The formula would return:

2321
72833
02931

That would help me a lot.

Thanks.

This works on your posted sample data.

=TRIM(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),5))

 

[Flicker]

With sample text in cell A1

Try this...

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

Example
with A1: Store 02931 8A - Oakville 04-20-2011
the formula returns: 02931


Is that something you can work with?

I saw a formula like this two time but I'm not understand how does it work. Could anybody show me what behind the curtain, please?

 

[canadian86]

Thanks, they all work!