Extracting data from csv file from bank

[Bruno Trafaria]

Hi, everyone

I'm extracting data from a credit card bank file and i'm gettin this:

00000266123,"5534.****.****.0123","B.A. FERNANDES RODRIGUES","2022-06-08","2022-06-09","Sligro Zb 5024 Rotterdam NLD","","","","-401,73"

00000266123,"5534.****.****.0","B.A. FERNANDES RODRIGUES","2022-06-08","2022-06-09","Toolstation Capelle A/ Capelle A/D INLD","","","","-23,68"

00000266123,"5534.****.****.0","B.A. FERNANDES RODRIGUES","2022-04-25","2022-04-26","Toolstation Capelle A/ Capelle A/D INLD","","","","-9,58"


I started with: LEFT(A2;33)
Then, a lot of: MID(A2;36;24), MID (A2;63;10), ETC, ETC...

My problem is the final number (the amount of the transaction). It can have 7 characters (-401,73), 6 characters (-23,68); 5 characters (-9,58), etc...

I don't know how to extract that number with a single formula. It's the number BETWEEN THE LAST 2 QUOTATION MARKS. Any formula to solve this?!?!


Thank you so much?

[PaulSP8]

Try;

Please Login or Register  to view this content.

[6StringJazzer]

Why don't you just open the file in Excel as a comma-delimited file? Then the final number will be in column J.

[Bruno Trafaria]

AMAZING JOB. I can愒 thank you enough!!!

So simple! THANK YOU SO SO SO MUCH!!!!!!!!!!!!!!!!!

[Bruno Trafaria]

Hi.

I tried to but it just opens directly in Excel without asking me anything! All the info is already in cells A1, A2, A3, A4...etc.

My boss sent me the file, probably he changed something before. Is there anyway to go back and do it that way?

Thanks a lot

[6StringJazzer]

Is it a .csv file, or is it an Excel .xlsx file?

If it opens in Excel with all the data in column A, you can do Data > Text To Columns and select "," as the delimiter. However, it may also use commas that are inside the quotes, which would be bad.

EDIT: I tried it with your sample data and it worked fine.

[Bruno Trafaria]

Hi.
Sorry for the late reply.

You are absolutely right. I had never used "Data > Text To Columns and select".
Amazing tip.

Thank you very, very much!!!

[Bruno Trafaria]

Hi, again!

Sorry to trouble you.

In the eventually of the amount being positive (therefore, without the minus sign), this formula will not work, right?

Is there a formula that works for both cases?

Thanks in advance

[6StringJazzer]

I assume you are asking PaulSP8. Text To Columns works fine either way.

[Bruno Trafaria]

Ups...sorry!!!!

[Bruno Trafaria]

Hi, again!

Sorry to trouble you.
In the eventually of the amount being positive (therefore, without the minus sign), this formula will not work, right?
Is there a formula that works for both cases?

Also:
Your formula finds: """-" but what i have in the text is "","-
Why does it work if the comma is missing?!?!

Thanks again, you did an amazing job



Thanks in advance

[PaulSP8]

If you need a Formula that works in both cases then you can use;

Please Login or Register  to view this content.

It didn't occour to me that there could be negative and positive numbers as all your samples included the negative - Sorry about that.

Just so you now what's going on;

The original formula is looking for "- as that only looked to appear once in the cell - Once I found the position of that I used MID to find it once as a starting point, then I could use that same position to work out what the rest of the value was past that point my taking off the remaining length of the value and removing the final "

The new formula works by replacing all the "'s in the value with a 99 spaces (" ") and then taking them away again using TRIM and RIGHT until you're left with just what is between the last two.

(Please excuse the poor explanations - I've got Covid at the moment and the brain fog is very much in play)

[Bruno Trafaria]

Hello.
I'm so sorry about your current situation.

I understand if i don't get an answer but i'm struggling with the original formula.
You wrote:The original formula is looking for "-

2 questions:

1) If the original formula looks for "-, shouldn't it be FIND(""-",A1)? -> (one less quotation mark)
2) Nevertheless, my data endes with (example): ","-401,73". Shouldn't the formula be: FIND("","-",A1)? -> (one less quotation mark and one comma)

Your formula works perfectly but i just don't understand how! What am i thinking wrong?!

THANK YOU SO MUCH AND GET WELL ASAP

[PaulSP8]

LOL - Honestly Bruno, I'd forgot about the """" thing - It's just something I do now.

"""" is just a way of saying you're looking for a Single ". The first two pretty much cancel each other out and so "- becomes the string you are looking for as it's followed by another ".

I'd be lying if I said I understood this completely, it's just something I'm used to doing now and that's how it is in my head.

So, looking at your '00000266123,"5534.****.****.0123","B.A. FERNANDES RODRIGUES","2022-06-08","2022-06-09","Sligro Zb 5024 Rotterdam NLD","","","","-401,73'

Please Login or Register  to view this content.

There are no other instances of "- so that's something I can look for to get your number.

So, I use '=FIND("""-",A1)' to get me the position of 128 which is actually just the " - But does give me the starting point of "- so I need to use '=FIND("""-",A1)+1' to get me to position 129 and the -

What Excel is seeing so far is;

=MID(A1,FIND("""-",A1)+1,LEN(A1)-FIND("""-",A1)-1)
=MID(A1,129

To get the number I don't need to know where the ',' is at all, I think you might have misunderstood something there about what the Formula is doing

To get the full number I need to know how far away is is from the end to get the number of characters I need, so I use LEN(A1) to get me 136 and then I take off the number of characters to "- which is 128

136 -128 allows me to get to how many characters there are left in the string after "- which is of course 8

=MID(A1,FIND("""-",A1)+1,LEN(A1)-FIND("""-",A1)-1)
=MID(A1,129,8 (136-128)

I know there is the " at the end of the string so I actually want one less then 8, which is 7 so I do the -1 at the end.

So we end up with Excel seeing;

Please Login or Register  to view this content.

I hope that makes sense now?

[6StringJazzer]

"""" is just a way of saying you're looking for a Single ".

Anything in between "double quote marks" is a text string. If you need to include a double quote mark within a text string "then you must use a """"pair of them"""" wherever you need one". It's not exactly canceling out, it's more like an escape to say, "This is not the end of the string yet!"

[Bruno Trafaria]

Hey!

Hope you are feeling better.

Thanks for the whole explanation but i'm (still) just struggling with the FIND("""-",A1) part. You also wrote: "I'd be lying if I said I understood this completely", so it愀 normal i can't understand it either. Still doesn't make sense to me...

Both your formulas work fine and i'm very thankful for what you did.

I am already looking for further explanations online, so i can ease my mind about this!

Cheers!!!!

[Bruno Trafaria]

Hi!

Thanks for trying to explain but i really can愒 understand.
I'm looking for some answer online.
Thanks for everything!

Regards!