Test for Data Type

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
Is there a way to test a piece of data to see if it is a string (or has the potential to be a string) or an integer (or the potential to be an integer)?

I am pulling lines from a text file and there is no easy way to delimit the data so I am having to split the line based on different characters (like "-", and ".") and hopefully be able to test the data type potential to get the values that I need.

Thanks in advance, and Happy Thanksgiving to my USA peeps!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Well for numbers you can use IsNumeric.

For text/strings it's kind of harder - anything can be in a string.

If you are splitting the data based on delimiters why do you need to know the data type anyway?
 

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
Well for the most part, the data is in a non-standard format (it is my bank statement). They output a date like 8-02 for August 2nd, but the same line can (and usually does) contain another "-" in the amount of the transaction, i.e., 7.09- for a $7.09 debit. But the description of the transaction can contain a "-" as well, and that is really what I need to check to make sure that I am pulling the numeric value and not the text which would cause an error.

I think that the IsNumeric will probably be what I need to help me test this. Never even knew that existed. Thanks for the help!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Don't you know what order the data is in?

eg date, description, amount...

Do transactions always have a $?

How about the dates, are they always formatted in this way?

Are the field lengths fixed?

It might be worth checking for things like that.
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,308
Office Version
  1. 365

ADVERTISEMENT

If you're looking for a pattern in the data, I suggest you post some sample data. Someone will decipher it one way or another to help evolve a pattern.

It reminds me of an old security issue. This security is perfect, no one, but no one could ever crack this. Ya right, put it out as a challenge to a group of first year college students, and they'll show you a new meaning of perfect.
 

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
No unfortunately, I discovered that the data is not exactly the same everytime I download my statement. Sometimes, the dates and transaction ID's are on the same line as the description and amount (which does not use a "$" by the way...) and sometimes they are separated and on different lines - even within the same file.

The date is always formatted as ##-##, or #-## depending on the digits in the month, so even that is not one hundred percent standard. I know the problem comes that it is downloaded as a PDF and I convert it to text to be able to read it.

So, in short, there is no pattern that can be duplicated from statement to statement. There are some key items that I can use to flag when the transactions section occurs and some other items, but as far as the individual transactions are concerned, they are a mess.

But, any other ideas/direction is always appreciated and I will always consider it. Thanks!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I take it you can't get it in another format, eg CSV, text.
 

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
No, I wish I could get it as CSV because then I would be more confident that the data would be standardized. Maybe that is something that I could suggest, but doubtful that it would be implemented any time soon.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Seems a bit strange all they offer is a PDF.

I would have thought a text file would be easier for them.:)
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
As mentioned, it's hard to give any advice without seeing sample data. But you may want to put it in a good editor (such as Notepad++) and see if there's any hidden characters that might help -- i.e., tabs.

BTW, you don't really need a bank statement if you are entering your transactions correctly, except to mark off cleared checks. I think banks should offer more than one format, but sometimes you need to look for not a "Statement" but a "Transaction History", which may be downloadable from the same screen where you view transaction detail.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,119
Messages
5,622,834
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top