remove decimal from field, append what was after decimal to before

[silent122bob]

Hi, I am trying to format some census tract data for a class project that has some cells that show up in the format "1234.56" but would like to remove the decimal and retain what was after it. the only manual way I can think of to do this would be to script text to columns to use the period as the separator, then send the decimal value to an arbitrary value that can just be recycled to hold the data and then maybe use an activecell & <arbitrary cell> to recall it. I'm stuck as to how to set the destination for the decimal data.

Thanks in advance for any tips you can offer.

p.s. i have to run out to take care of some things, so i appologize for any late replies.

[Paul]

Hi Bob, welcome to the forum.

You should be able to simply multiple all the cells by 100. That will move the decimal to the right two places. You can type 100 in a cell. Select that cell, then copy it. Select your range of census data and right-click > PasteSpecial > Multiply. You can then delete the cell with 100 in it.

[martindwilson]

you can also use find replace on numbers,replace . with nothing

[Russell Dawson]

How about in B1 where data is in A1
=RIGHT(A1,2)
because as I read it all you want to do is retain the data following the decimal. Unusual I admit but that's how I read it. " . .remove the decimal and retain what was after it"
This relies on all source data being to two decimal places.

[silent122bob]

Thank you all for the swift replies. Paul and Martin, those sound like viable options. Russell, you are correct in your interpretation. I don't quite understand how your solution would operate, but since this is my first experience wording with excel data that's to be expected. Being in GIS I'm sure I will be Needing to pick up a lot of handy tools. I will try out them all when I arrive home to get a feel for how each weighs for different situations. Thanks again for the tips. The multiply by 100 is so simple I'm mildly blushing to not have fought about it. I was overwhelmed by what I would have to do to script it that I didn't consider a more simple approach. I hope to learn and share in the years to come.

[FDibbins]

thats the way i read it too, Russel, and my suggestion would have been along the lines of yours, either extract the left to the . so they can be used, or extract to the right after the . for later use

perhaps the OP can help us all understand better what he wants?

[FDibbins]

assuming your value is in A1 -

to extract the values before the . use......

=MID(A1,1,FIND(".",A1,1)-1)

to extraxy after the . use...

=MID(A1,FIND(".",A1,1)+1,5)

[Russell Dawson]

Very nice.

[silent122bob]

sample data has 3 main subtypes, a. 101, 102, etc b. 1000etc c. 1000.01 1000.02 ... 4803.01 etc

From the stance I started with, it started with thinking of it as a text field, so I wanted to attach a lead and 2 trail zeros, so,

Please Login or Register  to view this content.

I was thrown off that it didn't seem to work, which reminded me that I needed to check my format settings (some strange flashback to freshmen year). this bit of code with the removal of the lead zero for four digit entries seemed appropriate but the decimal kind of threw a wrench into the plan.

Now this leads me to think about what functions I should look into to decide which needs what. I'm thinking

Please Login or Register  to view this content.

I'm not entirely sure if syntax is correct, however i can worry about that later. I feel I should be able to put together something. I'll post a more final design while I mix and see what I can break.

[martindwilson]

why didn't you just find replace . ? if it didn't work please tell us why?

[FDibbins]

Martin, in his 1st post, he said he wanted "but would like to remove the decimal and retain what was after it." i took that as remove the data after the . and retain it for later use...i could be wrong though, reading it again, you can read it both ways. his 2nd post (post #5) seems to confirm my read though?

[martindwilson]

i read that as 23.23 becomes 2323 !

[Russell Dawson]

He told me that I was right (post #5) in my interpretation i.e. "retain the data following the decimal", which I took to mean solely the data after the decimal. I suppose the clue is in the title! Or not!

It just goes to show how important it is to ask accurate questions.

Pot-ay-ta, pot-ah-ta - tom-ay-ta, tom-ah-ta

Lets call the . . . . .

[silent122bob]

example starting cell format before parse(this will make sense if you read below)"Census Tract 1, Allegany County, Maryland"

so here's what i came up with for my code. i format the column to text first so lead zeros stay, then i run parse first, (data actually is formatted with the county and state as well, and since my last post i found it would be better to save them so parse may not follow what my original posts said)
then i run leadzerounder100000 to append lead zeros to values that are under 5 digits after multiplying by 100.

martin, though the find ad replace would have worked for entries with a decimal, multiplying by 100 did the same thing, since if entries were formated "1234.1" vs "1234.01",(this time around they didn't have any like this however a different state's tracts may be different) the first would need a trail zero, since with census tracts are always 6 digits i would have to add an extra piece of code to append a trail zero, and at that point i would have forgotten which ones needed to be weeded out.

Please Login or Register  to view this content.

this project has definitely made me think about things more modularly. when i started i had macros in too many different modules and files, but now have cleaned it up and broken down into the bare necessities. thank you for all the help. if you see any things i could make it go faster, please feel free to pitch in. my current mode of automation is to hotkey the 3 functions then hold the hotkey down until all lines are finished =P (this would suck for 10,000's of records)