Integer vs long vs decimal

**mtclimber** · 02-23-2009, 09:53 PM

Hello,

Take a look at the sample sheet. I get the information as an import that looks like the info in column A.

I want it to look like the data in Column B. If I try to format as a number it always turns the last number to a 0. I also dont want the 0's on front of the digits either.

I dont really care how its done. But I would like a routine to turn column A into and like the example in Column B. No zeros on the front and maintaining all numbers.

Thank you for suggestions.

**shg** · 02-23-2009, 10:04 PM

You only get 15 digits of precision; if you have numbers larger than that, you need to format as text.

**mtclimber** · 02-23-2009, 10:06 PM

Can you help me write a routine to get rid of the zeros on the front. These strings will be searched for, and I dont want to have to type 0's in the search box. Unfortunately I dont know a whole lot about strings.

**shg** · 02-24-2009, 01:18 AM

Assuming there's a non-zero character in the first 10 characters,

=MID(A1, MATCH(TRUE, MID(A1, {1,2,3,4,5,6,7,8,9,10}, 1) <> "0", 0), 255 )

**mtclimber** · 02-24-2009, 09:18 PM

Hello,

I tired the line of code you made for me. I placed it in mine and I cant get it to work. The compiler does not like the brackets. So I took the brackets out and the compiler accepts the line. When I try to run, it gives me an error that there are to many arguments. Please refer to the attached xls sheet for example.

Thank you again for your time.

**shg** · 02-24-2009, 09:47 PM

It's a formula. Put it in B1 and copy down.

**mtclimber** · 02-24-2009, 10:44 PM

Well, I want to run it in VBA. Did you look at the code in the example sheet? I cant just copy the formula into B1.

Needs to be run in VBA

Thanks again for your help, still open to suggestions.

**shg** · 02-24-2009, 11:36 PM

Please Login or Register  to view this content.

**Leith Ross** · 02-24-2009, 11:58 PM

Hello mtclimber,

Here is a VBA macro that can be reused in your code. The macro resides in standard VBA module and not in the Sheet module. This allows you to use the macro with any worksheet and with any column of data in your workbook. This code has already been added to the attached workbook and to your command button.

Module1 Macro Code

Please Login or Register  to view this content.

Worksheet1 CommandButton Code

Please Login or Register  to view this content.

**mtclimber** · 02-25-2009, 08:18 PM

Leith Ross,

Thank you for your help. I adapted your code to fit my project. I came across a problem which I worked out. And now it works fine. The problem was that everytime you clicked the command button it would continute to remove a digit until all the digits were gone, or until a empty cell came through.

Also,
I am new to coding. I have noticed that just about everyone has a different way to range areas. Although I did not use your way, I wanted to know what benifits it may offer. My project already had it's range established, I just needed the code for the MID functions and the loop.

Again, thank you for your time. Attached is the finished code, adapted to axample of my project.

Thanks again

mtclimber