searching a cell for a 7 digit number - please help

[zakajam1980]

Good morning all,

wondering if you could possibly help. I'm trying to search a column that has cells with various length strings. Included in the strange (at random locations) could be possibly be a 7 digit number starting with 4. I want a formula that counts the number of cells with contain that 7 digit number. Is this possible?

Thanks in advance for your help.

Jamil

[Fotis1991]

Hi

Assuming that your data start in A2, in B2 and copy down use this formula. This will be a helper and hidden column.

Please Login or Register  to view this content.

Then use

Please Login or Register  to view this content.

to get your result.

[hemesh]

or you could use like =SUMPRODUCT(--(LEN(A:A)=7)*(LEFT(A:A,1)="4")) assuming your data starts in column A

[ben_hensel]

Here is my solution.

A1 = first text string: A2 is second, A3 is third, etc.

B1 = LEFT( RIGHT(A1, LEN(A1) - FIND("4", A1) +1), 7)
Works by:
1) Find the position of the first instance of the number "4" in the string,
2) clipping off everything to the left of that position,
3) clipping off seven digits rightward of the left-clipped value

C1 = IF(B1 > 1000000, 1, 0)

Drag B1 and C1 down.

D1 = SUM(C:C)

(I would have used a COUNTIF in C1 instead of another helper column but I see that you are using Excel 2003?)

Then D1 will give you the number of text strings that contain a 7-digit number that start with "4".

Caveats:
1) If the number doesn't start with 4, this formula won't return it.
2) Something like abc4def4123456
will return
4def412 (text, not a number)
not
4123456 (the 7-digit number)

EDIT:
Aaaand I double-guessed myself and it turns out that excel 2003 had COUNTIF after all, huh?

[daddylonglegs]

I'm trying to search a column that has cells with various length strings. Included in the strange (at random locations) could be possibly be a 7 digit number starting with 4. I want a formula that counts the number of cells with contain that 7 digit number.

I'm assuming that your strings could look something like this:

"xxyy abc 4444455 adf sgh"

Will the 7 digit number be the only number in the string? Could there be 6 or 8 digit numbers that you don't want to count - I assume that the 7 digit numbers can be different numbers in different cells, is that the case?

Perhaps you could supply a small sample of the data (5 to 10 rows)?

[zakajam1980]

Thanks for your replies so far. The sample could contain any random text as it is part of conversation download. Yes you are right the 7 digit number could be completely random but will always start with a four. I've included a sample sample.

Again really appreciate all your help.

[daddylonglegs]

If you use this "array formula" in C2

=COUNT(IF(MID(B2,ROW(INDIRECT("1:"&LEN(B2)-6)),1)="4",MID(B2,ROW(INDIRECT("1:"&LEN(B2)-6)),7)+0))>0

confirmed with CTRL+SHIFT+ENTER

that will give you TRUE if B2 contains a 7 digit number beginning with 4

[hemesh]

Try This array formula in C2 (Copy paste Below Formula, Hold control and shift then hit enter to confirm it as an array)
=MID(B2,MATCH(TRUE,ISNUMBER(TRIM(MID(B2,ISNUMBER(SEARCH(" ",MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)))*ROW(INDIRECT("1:"&LEN(B2))),8))+0),0),8)+0
Then Drag down this will extract the numbers in the formula.

Now in D2 Enter below array formula
=SUM(IFERROR(LEN(C2:C11)=7,0)*IFERROR(LEFT(C2:C11)+0=4,0))
This will give you the count based on 7 characters and 4 in the start

[hemesh]

Find the attached.

[zakajam1980]

Guys thanks for your responses, I used the last one and appeared to work well.

Much appreciated!

[daddylonglegs]

Don't you need to count B6 which does contain a 7 digit number beginning with 4.....or doesn't that count?

[hemesh]

Don't you need to count B6 which does contain a 7 digit number beginning with 4.....or doesn't that count?

Dear Sir, Good point to bring in notice I have Taken spaces into consideration, How below will be
zakajam1980 try this in B2
=MID(B2,MATCH(TRUE,ISNUMBER(MID(B2,ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)+0)*ROW(INDIRECT("1:"&LEN(B2))),7)+0),0),7)+0
confirm with control shift and ENter