Finding specific strings

[steve astrop]

Hi, Im not really sure of the formula I need so I havent suggested. I have tried len, Istext etc but got stuck.

I have a combination of letters and numbers in a column. For example.

xx1234567x
1234567xx
xx1234xx1234

I need a formula to tell whether they begin with two letters and have a certain number of numbers then end in letters. Or have letters in the middle. Sorry Im so vague, I just need a start in the right direction. Ideally combined with an IF statement.

In english (If two letters afetr nine numbers then yes)

Kindest regards

Steve

 

[Norie]

Steve

None of your examples have 9 numbers?

Unless the x's could be numbers as well as letters.

 

[steve astrop]

Yes well spotted!
Sorry I just used 9 as an example it could be any number. the XX are letters and the 12345 are numbers. Thank you for your time in this matter

 

[steve astrop]

I have foun this formula which tells me part of the problem.

=IF(AND(ISTEXT(LEFT(A1,1)),ISNUMBER(MID(A1,2,9)+0)),1,0)

How do I tweak this for multiple criteria? for example begining with one letter, 9 numbers and ending in two letters?

Thanks all...

 

[pgc01]

Hi Steve

A flexible way is to use a udf and a mask to define the specific configuration.

This is an example. We buid a mask using

"@" for a letter
"#" for a number

ex.:
"begining with one letter, 9 numbers and ending in two letters"

mask: "@#########@@"

"beginning with 2 digits, 2 letters, 3 digits and 1 letter"

mask: "##@@###@"

Use the udf in the worksheet like this:

=alphanum(A1,"@@#########@")

Function AlphaNum(s As String, sMask As String) As Boolean
AlphaNum = s Like Replace(sMask, "@", "[a-zA-Z]")
End Function

 

[pgc01]

P. S.

Notice that the formula you posted does not solve your problem at all.

ISTEXT() does not tell you that you've got letters, it just checks if it's text which means a lot of other things besides letters.

ISNUMBER() does not tell you that you've got just digits, it tells you that it's a number.

 

[steve astrop]

Thanks very much for the response. . I think I have used it correctly..

How do I get it to return more than true or false?

Kind regards

Steve

 

[steve astrop]

Sorry I have tried this and can only get it to return false. I have added teh code in the moudule and used teh formul in a cell?

What am I doing wrong?

Regards

Steve

 

[steve astrop]

It seems I was wrong (not for the first time!)
The formula and module do work but only when I re-enter the values in the cell.
It doesnt work on existing data? I assume I need to re-calculate or something?
When I paste the formula in it looks like it re-calculates but it still reports false.
Howevere if I retype the value in the cell it changes to true.

regards

Steve

 

[Aladin Akyurek]

Another option...

<TABLE style="WIDTH: 273pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=364 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=108> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64>Non-Digit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64>Non-Digit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>TEXT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>DIGIT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>TEXT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Result</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>QW123556609ux</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=AND(COUNT(MID(A3,ROW(INDIRECT("1:"&B3)),1)+0)=0,COUNT(MID(A3,ROW(INDIRECT(B3+1&":"&B3+1+C3)),1)+0)=9,COUNT(MID(A3,ROW(INDIRECT(B3+C3+1&":"&B3+C3+1+D3)),1)+0)=0)+0' x:arrayrange="E3">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Q4123556609ux</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=AND(COUNT(MID(A4,ROW(INDIRECT("1:"&B4)),1)+0)=0,COUNT(MID(A4,ROW(INDIRECT(B4+1&":"&B4+1+C4)),1)+0)=9,COUNT(MID(A4,ROW(INDIRECT(B4+C4+1&":"&B4+C4+1+D4)),1)+0)=0)+0' x:arrayrange="E4">0</TD></TR></TBODY></TABLE>

E3:

Control+shift+enter, not just enter...

=AND(
   COUNT(MID(A3,ROW(INDIRECT("1:"&B3)),1)+0)=0,
   COUNT(MID(A3,ROW(INDIRECT(B3+1&":"&B3+1+C3)),1)+0)=9,
   COUNT(MID(A3,ROW(INDIRECT(B3+C3+1&":"&B3+C3+1+D3)),1)+0)=0)+0

1 means a hit, 0 a non-hit.

It should be noted that the formula is a bit costly, due to the volatile INDIRECT...