extract numeric parts out of a string

[Constant]

Hi every one,

I receive on a daily basis from several banks (from several countries) strings which contains payments from which the value is numerical places in a string. The difficulties relies in the following form:

1- The numeric may be with decimal parts (not Always) and the separation may be a dot or a comma (ex 123.45 or 123,45). But there is always on of the separators

2- The number is always follow by a money symbol (EUR, €, £, $ no more) but between the number and the symbol either there may or not may a space

3- The number may be bigger than 999 but the separator between the hundreds and the thousands may be or not be present. If present it may be a dot, a space or a coma but never the same as the decimal separator(ex: 1 234,45 or 1234,45 or 1,234.45 or 1.234,45)

4- Positive number are not indicated by a “+” while negative number are always preceded by a “-“which is always just before the number without any space.

5- Last but not least between the word just before the number and the number sometimes there is a space and sometime not.

Here some trivial ex:

- “the best bank in the world pay you -45 578,53€ just for your fun”

- “the best bank in the world pay you45 578.53 $ just for your fun”

- “the best bank in the world pay you 45 578.53$ just for your fun”

- Ect…


Can this be solved by formulas with or not with helper’s column?

If possible without VBA but not excluded


I put manually the numbers in a separate column and filter it with my brain, but human being are making mistakes, hardly to find afterwards.

Thisd should be a great help for me and spare à lot of time.

Thanks in advance

C. Peten

 

[David Evans]

Provide some real examples of the data as it arrives for you. If we can identify the patterns of the incoming data, you'll likely get an answer.

Make sure it's in an excel file as you receive it ...

 

[Constant]

Provide some real examples of the data as it arrives for you. If we can identify the patterns of the incoming data, you'll likely get an answer.

Make sure it's in an excel file as you receive it ...

I am sorry, these "examples" are confidential and i am not aloowed to make it public. The files comes from different banks and therefore there are variation in the format. The 5 points above are the "different patterns" that I was able to fix based upon hondereds of files.
I also "simulate" 3 exemples thazt are typical of the files I received.
Tha

 

[Constant]

I am sorry, these "examples" are confidential and i am not aloowed to make it public. The files comes from different banks and therefore there are variation in the format. The 5 points above are the "different patterns" that I was able to fix based upon hondereds of files.
I also "simulate" 3 exemples thazt are typical of the files I received.
Thanks for your interest and sorry that I can't give you life exemples.
Best regards
C. Peten

 

[shrivallabha]

Assuming your data is in cell A2 following formula shall work:
=IF(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,1)="-","-"&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LOOKUP(2,1/ISNUMBER(MID(A2,ROW($A$1:INDEX(A:A,LEN(A2))),1)+0),ROW($A$1:INDEX(A:A,LEN(A2))))+1-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))),MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LOOKUP(2,1/ISNUMBER(MID(A2,ROW($A$1:INDEX(A:A,LEN(A2))),1)+0),ROW($A$1:INDEX(A:A,LEN(A2))))+1-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))))

Not really good from aesthetics and certainly irritating from maintaining point of view. I am quite sure there will be better alternatives than this.

Only good thing is it doesn't rely on currency symbols to locate last digit. I have attached file for your ease.

Assumption: You are dealing with one numerical portion i.e. not like
hello 123 this is 124 calling!

 

[NARAYANK991]

Hi ,

The wiser , whether better or not is debatable , alternative is to use 2 helper columns.

Narayan

 

[shrivallabha]

I agree completely. I forget sometimes and just carry notion that no helper column shall be used.

One minor point (may not be necessary) is that the dash and minus are the same symbols so the formula will give erroneous results for data like where extra dash may be present:
the best bank in the world - pay you -45 578,53€ just for your fun

I thought of it as important as OP has mentioned it in his point #4 in post #1.

 

[NARAYANK991]

Hi Shrivallabha ,

Valid point ; one more helper column !

Narayan

 

[bosco_yip]

Another option,

In B2, copied down:

=LOOKUP(9^9,0+RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,MIN(FIND({"EUR";"€";"£";"$"},A2&"EUR€£$"))-1),",",""),".","")," ",""),ROW($1:$250)))/100

Regards

 

[Constant]

Thanks a lot for all these helps.
I will use helpers columns and try to digest all these formulas.
It is a great help for me.
Beste regards from Bruges in Belgium
C. Peten