Extract value from text string

[Tapyr]

Dears,

I would like ask you for help with this case.

I have list of movements where can be different position of currency and it is always in text format (e.q. blank positions between digits, wrong decimal mark, ... ).
I want to find some formula which will understand what is the number in text string and also determine valid decimal sign.

You can find example list in attached file with result what I need to retrieve.

Thank you very much for you help.

[FlameRetired]

This is not my formula. I've "borrowed" it from XOR LX who previously borrowed it from Lori.

You will need to array enter it.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

In C2 and copied down.

Please Login or Register  to view this content.

[FlameRetired]

It looks like this:

	A	B	C
1	Movement	Amount	I need:
2	Debit	USD 4 884,59	4884.59
3	Credit 1	-41,52 EUR	-41.52
4	Credit 2	-1,65 EUR	-1.65
5	Total	4 841,42	4841.42

[FlameRetired]

and also determine valid decimal sign.

I messed up on that part and I don't recall what the formatting options are for your area.

[Tapyr]

This is not my formula. I've "borrowed" it from XOR LX who previously borrowed it from Lori.

You will need to array enter it.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

In C2 and copied down.

Please Login or Register  to view this content.

Hi,

thank you very much for you quick response. At first what is strange for me now is, that I used this formula without array-entered and it looks, that it is working :-). The result is what I am expecting, but I am really suprised why I don't need to use this Ctrl+Shift+Enter action. I don't want to believe in miracles :-).
Anyway if I want to use this formula in macro, can I ensure in VBA this array entered condition?

[FlameRetired]

If you are not having to array enter (CSE) it sounds like your Office version is 365. I so please update your profile. Members often consider that when offering solutions.

glad to hear it works. Out of curiosity what are the formatting options you used? I couldn't over ride my USA settings.

[Tapyr]

Yes, I am using 365. I didn't realize it, that I still have 2010 in my profile. :-). Going to change it.

I am using US settings as well so I just copied/paste your formula and that's all :-). It worked perfectly.

Once again, thank you very much. I am going to close this thread and send you some reputation points and of course, change my Excel version in profile .

Thank you and have a nice day

[FlameRetired]

Glad to help.

Thank you for your response, updating your profile, added rep and marking your thread Solved.