Is it possible to recognize a number in a textbox?

[ifritaeon61]

Hello everyone,
Out of curiosity, I was wondering if it is possible to recognize a number inside a cell which has something else written in it, for example "There were 5 apples -> There are 4 apples".
If so (it would be amazing), is there a way to tell if a number is bigger than the other?

Please take this question as if it was asked by a child. I don't even know if it would be easier to ask Excel to make a coffee instead of this, at this point!

[achammar]

You'll probably have to use VBA. Do you know how to do that? Put this in a module. This works on cells C3 and C4 only. I've attached a sample workbook as well.

Please Login or Register  to view this content.

[mehmetcik]

So extract the number from a cell.

The answer is yes. If your number is in A1 then:

=CONCAT(FILTER(MID(A1,SEQUENCE(LEN(A1)),1),ISNUMBER(1+MID(A1,SEQUENCE(LEN(A1)),1))))

[jindon]

for example "There were 5 apples -> There are 4 apples".
If so (it would be amazing), is there a way to tell if a number is bigger than the other?

Since I don't know how you want, the code will output the result in the cell to the right.

Please Login or Register  to view this content.

[ifritaeon61]

Oh my God, all your solutions are brilliant! Thank you so much!

Since I don't know how you want, the code will output the result in the cell to the right.

Hello jindon, your macro answers perfectly to my needs (even though achammar and mehmetcik's advices are great and both helped me learning a lot more). You gave me a good idea and I would like to work on it!
May I ask you what I should do if I had something like "There were 5 apples and 3 oranges -> There are 4 apples and 6 oranges"?


P.S. Your macro works even with merged cells...Amazing!

[jindon]

Try change to

Please Login or Register  to view this content.

[ifritaeon61]

Try change to

Hello jindon,
I'm sorry for replying so late, but today I had some issues I had to solve, unluckily...
I tried your macro, but I'm very tired and I must have made a mistake somewhere, because it gives me an error.

Would you mind if we continue our work tomorrow? I would be able to be more precise about what I would like to achieve.

Thank you very much for your kindness, anyway. I hope you still want to help me.


Edit:

Hello, finally I'm back...
If we had something like my previous example: "There were 5 apples and 3 oranges -> There are 4 apples and 6 oranges" would it be possible to sum the first two numbers and compare them with the last two? In this case it would be 8 vs 10 and I would like to obtain "Improved" as a result.
On the other hand, if we get a smaller result, I would like to get "Pejorative".

I would like to thank you and whoever wants to help and apologize for "disappearing" for some days...

[ifritaeon61]

First and only bump, just because I realized that editing a post doesn't put in evidence a thread.

[Rick Rothstein]

Does this stand alone macro do what you want...

Please Login or Register  to view this content.

[jindon]

=IsImpPej(A1)
Change to

Please Login or Register  to view this content.

[ifritaeon61]

Hello Rick and jindon and thank you for answering!
I tried both your macros:

Does this stand alone macro do what you want...

It does exactly what I would like to obtain! The "problem" is that it works just with the first row.
To make think even harder (my fault for not pointing out before, I'm sorry) in my case, I have a column with merged cells. Do you think it's possible to apply your idea?

=IsImpPej(A1)
Change to

It looks like I'm not even competent enough to understand where your macro starts...
I tried doing like this:

Please Login or Register  to view this content.

But I obviously made a mistake!
May you post the entire code, please?


Thank you both for your patience!

[Rick Rothstein]

Hello Rick

I'm sorry) in my case, I have a column with merged cells.

You should have mentioned that in your first message as merged cells affect how a macro is coded. Are all the cells in Column A part of a merge or do you have individual non-merged cells mixed in? Are the number of cells in an individual merge the same for all your merges or can the number of cell within individual merges vary? If there are the same number of cells in each merge, how many cells is that and what is the cell address of the first merge in Column A?

[ifritaeon61]

You should have mentioned that in your first message as merged cells affect how a macro is coded. Are all the cells in Column A part of a merge or do you have individual non-merged cells mixed in? Are the number of cells in an individual merge the same for all your merges or can the number of cell within individual merges vary? If there are the same number of cells in each merge, how many cells is that and what is the cell address of the first merge in Column A?

I'm sorry, I didn't even know that all this was possible. I just tried to ask a question and I unexpectedly received unswers!
You can understand that, to me, this is like magic. 😉
Also, the kvrisnamurti's first macro actually works with merged cells, so I erroneously stopped worrying about it.
Again, my bad.

Now your question: all the cells in the column are a part of a merge and there aren't any singular cells.
Also, the number of individual cells in a merge can vary.

Thank you very much for your patience. 😉

[jindon]

Just use the code in post #10, and enter B1:
=IsImpPej(A1) and fill down.

Or change "test" sub to

Please Login or Register  to view this content.

[Rick Rothstein]

I haven't test it but I would guess jindon's code will be (much I would imagine) faster than my code so use his if you can get it to work for you. However, I did not want you to think I abandoned you so here is the code I came up with to handle your merged cells...

Please Login or Register  to view this content.

Note: Personal Opinion... you will do yourself a favor by avoiding Merged Cells in future worksheets you develop as they tend to be a real pain to work with..

[ifritaeon61]

Hello jindon and Rick,
I really must thank you both, because you managed to do something which, in my eyes, is incredible!
Both your macros work perfectly. Tomorrow I'll test them better and will see if I'm capable enough to adapt them to my case.

Note: Personal Opinion... you will do yourself a favor by avoiding Merged Cells in future worksheets you develop as they tend to be a real pain to work with..

I'll remember your advice and don't worry, I would have never judged you if you had left my thread for any reason of yours!
Thank you for your kindness, anyway!

[ifritaeon61]

Hello Jindon and Rick,
I feel I must apologize for disappearing for so long. I had my own reasons, but still my behaviour was not fair to both of you.
I will gladly assign to both of you your deserved reputation for your patience and professionality.

Regarding the thread, I would like to keep it open, for now, because this project might be interesting and I would like to work on it when I have some more time.

Thank you very much!
ifritaeon61