• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find stand-alone number/integer in a text string

Rediska

Rediska

Member
Hi Excellians,
I am working on a small validation project, and I am desperately need your advice on finding the stand-alone number in a text string from a single cell, returning an error message if found.
Cell example: “bla bla bla V409 bla bla (1000) bla bla 200” – formula has to skip V409 and find stand-alone 1000 and 200 (any stand-alone number), returning message type “not allowed or else!”.

How to do this with formula, not VBA?

Many thanks,
Rediska
 
Hello Rediska,
Can you clarify what you mean by "stand-alone"? From your sample, it appears that () still qualifies as stand-alone, but if the number is preceded by a single letter, it is not.

Are there any other conditions that we should be aware of?

Please upload a sample file that is representative of the conditions in your actual data.

Cheers,
Sajan.
 
Hi Sajan,

Thanks for quick respond!

Unfortuantely I can't upload the file, but for this specific cell entry options are:

1.number could be be in brackets of any type: (4000) or {5800}
2.number is always positive, sign is not shown: 50000
3.comma could be wrongly entered before and after the number without space: ,100 or 2000,
4.any other signs other than brackets or comma will not be used with the number

validation rules:
-if it is "attached" of at least to one charater, it should be skipped: V500, VG5600
-if at least one number is detected for options 1 and 3, warning message should be displayed:"wrong altitude input format"

Thanks again,
Rediska
BT, it's in excel2010 if it'll makes a difference

Sajan said:
Hello Rediska,
Can you clarify what you mean by "stand-alone"? From your sample, it appears that () still qualifies as stand-alone, but if the number is preceded by a single letter, it is not.

Are there any other conditions that we should be aware of?

Please upload a sample file that is representative of the conditions in your actual data.

Cheers,
Sajan.
Click to expand...
 
So, the following are considered stand-alone... Please confirm.
  1. Any number after a space, as long as it is followed by another space or end of string
  2. Any number that has a comma prefix or suffix, including "A,234,B" even though "A" and "B" are adjacent to the commas. Is this a valid condition?
  3. Any number in brackets: what are brackets? "(", "{" and "[", and their corresponding closing ones, or are there others?

Without more examples, the solutions we offer would be specific to the situations you describe above. Please verify if the three conditions described above are present in your data.
 
Hello Rediska,
Try the following array formula against a value in A3
=IF(SUM(N(ISNUMBER(FIND(LEFT(MID(A3, ROW(OFFSET(A$1,,,LEN(A3))), MMULT(FIND(" ",{""," "}&A3&" ",ROW(OFFSET(A$1,,,LEN(A3)))),{1;-1})),1),"[]{}(),0123456789"))))>0, "Bad","Good")

enter with Ctrl + Shift + Enter

I tried it against the following string and it seemed to work
bla {27} bla [45] ,500 bla 800, bla ,799, V409 bla bla (1000) bla bla 200

The assumption I made is that your string is space delimited. (i.e. it would not flag "V{23}" as a problem.)

Cheers,
Sajan.
 
Hello Rediska,
Here is one more array formula for a value in A3:
=IF(SUM(N(ISNUMBER(FIND(LEFT(TRIM(MID(SUBSTITUTE(A3," ", REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1))-1)*100+1,100)),1),"[]{}(),0123456789"))))>0,"Bad","Good")

enter with Ctrl + Shift + Enter

(Same assumptions as above.)

Cheers,
Sajan.
 
Hello Sajan,

Thanks, I've created a file to test your formulas - please see attached. In Red are all possible options to detect, in blue and such - to skip.
Results are interesting and a good start. I played a bit with the formulas but to no decent result.
It's a bit tricky, I understand - sorry for not being as clear as I should.

Cheers,
Rediska
 

Attachments

  • Test-Rediska.xlsx
    10.7 KB · Views: 7
Hello Rediska,
The formulas I supplied are array formulas, and need to be entered with Ctrl + Shift + Enter, instead of just the Enter key.
(you can confirm that it worked by noticing {} on both sides of the formula in the formula bar.)

Let me know if you are still seeing any issues.

Cheers,
Sajan.
 
Also, I noticed that the formula you are using is not the same ones I supplied.
Specifically, the comma got replaced by a semi-colon in your workbook. Please correct that.
 
Hi Sajan,
The semicolon is the same as a comma, it's due to global settings.
It is changed now (but not in the attachment I provided above - regardless, results are absolutely the same).
Rediska
 
I copied and pasted the formulas above into your worksheet and got the following results
sample text formula #1 comments formula #2
afdsa v3434 Good Bad
afdsa v3435 dsfsf Good Good
afdsa 3435, verr Bad Bad
afdsa v3436 Good Good
afdsa v3437, 4535435 Bad Bad
345 Bad Bad
sdad(455) sdfd Bad Bad
sdfh 23244; CWWT v344 Bad Bad
awee4567 sfdf v456 Bad Bad

Let me look at why formula 2 is supplying different results. But formula 1 is behaving as you are outlining.

-Sajan.
 
Hi,
There was an extra space in your first string that was throwing the second formula off.
Try the following instead:
=IF(SUM(N(ISNUMBER(FIND(LEFT(TRIM(MID(SUBSTITUTE(TRIM(A3)," ", REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(TRIM(A3))-LEN(SUBSTITUTE(A3," ",""))+1))-1)*100+1,100)),1),"[]{}(),0123456789"))))>0,"Bad","Good")
enter with Ctrl + Shift + Enter

I also noticed that if you have data such as "{abc}" both formulas will flag that as bad. Let me know if that is a condition you expect in your data.

-Sajan.
 
Regarding your comments about global settings...
If your data will contain commas instead of semi-colons, your global settings would not matter.
 
global settings - unfortunately it was causing formula validation error message and didn't allow proceed unless I change it
 
What I meant about global settings is that in the string "[]{}(),0123456789", the comma needs to be there if your input data will have commas. (For all other commas, please replace it with the delimiter that is valid for your version of Excel.)
 
Hi Sajan,

Finally - F2 helped, when it was possible to use Ctrl + Shift + Enter with the right results!
WORKS LIKE A MAGIC!
Thanks a lot, all good now!!!
 
Hi Rediska,
Glad you found a solution! Thanks for the feedback. Glad to help!

Come back any time!

-Sajan.
 
You must log in or register to reply here.
Back
Top