Find whole number in string

[sharky12345]

Really struggling with this one, must be a simple solution....

I need to search a column for an exact match to a whole number - each row contains a mixture of letters and numbers. Once found I just need to store the row number.

Note, with this though I need to be sure it does find precise matches, so if I searched for "2213" I would not want it to return a find for "22131".

The search range is sheet4 column A, (from A2 to the last used row), the value to search will change and I haven't decided how the user will carry out the search yet so for this purpose lets assume the search number will be "2213".

Can anyone help me please?

 

[vds1]

Any thoughts on why wouldnt MATCH formula wont work?

For example,

=MATCH(2213,A1:A10,0)

 

[sharky12345]

Hi, thanks - didn't think of that, will give it a go, though the search value and search range are on 2 different sheets - will try it and report back.

 

[sharky12345]

That suggestion doesn't work I'm afraid - it gives me 'N/A'

 

[Fluff]

Can you supply a few examples of your data?

 

[sharky12345]

Sure....

The search item will always be a number and can be 3, 4 or 5 digits. The search range will contain data that consists of both numbers and characters like this:

'TG 28726 HGHTYRDHJTRFV'

Not sure if it would make a difference but the string to search may also have a forward slash in it like this:

'T/GS 356 HFDSFGY'

Does this help?

 

[kweaver]

Is there always a space before and after the number? Is the number always the second "section" of the string?

If so, try this, if the number is in A1 (and fill down):

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)+1,LEN(A1)))

 

[sharky12345]

kweaver - thanks, yes, you're assumption is correct and your formula works for me...thanks!

 

[Peter_SSs]

kweaver - thanks, yes, you're assumption is correct and your formula works for me...thanks!

Your original post was about "searching" for a particular string (example was 2213) in a range of cells in column A. Has your requirement now changed to not looking for any particular number nor to look in a range of cells but just extract whatever the number is from each cell?

If the suggested formula in post 7 does actually do what you want, then this should also, given the assumptions and your information in post 6.

=MID(SUBSTITUTE(A1," ","  "),FIND(" ",A1),7)+0

 

[DanteAmor]

Just another way

=MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99)+0