Macros which affect data whee the numbers are formatted

ellison

Active Member
Joined
Aug 1, 2012
Messages
356
Office Version
  1. 365
Platform
  1. Windows
Hi all, our part numbers are entered onto Excel, and the cells are formatted as "text" in order to keep the part numbers in their original format.
Which is great, until we run a macro and the parts lose their formatting and return to

Examples

000456 becomes 456
1.10000 becomes 1.1

I sort of understand the "why" of why it's doing it, but more importantly don't want it to happen as in our industry the:

"456" relates to a plug and the "000456" relates to a bolt.
"1.10000" is a connector and the "1.1" is ermmmm gibberish.
icon11.png


The ways around it that we have thought of are to put a "_" at the beginning or end of each entry.

BUT that messes up other reports that we have going on, especially when we are merging data together.

Have you maybe come across this problem? and have you found a way around it?

Best

Neil
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Re: Macros which affect data whee the numbers are formatted...https://www.mrexcel.com/forum/images/smilies/icon_smile.gif

Have you tried placing an apostrophe at the beginning of each entry?
 
Upvote 0
Re: Macros which affect data whee the numbers are formatted...https://www.mrexcel.com/forum/images/smilies/icon_smile.gif

Yes same problem I'm afraid............
 
Upvote 0
Re: Macros which affect data whee the numbers are formatted...https://www.mrexcel.com/forum/images/smilies/icon_smile.gif

How about reformatting the cells as 'Text' at the end of your macro?
 
Upvote 0
Re: Macros which affect data whee the numbers are formatted...https://www.mrexcel.com/forum/images/smilies/icon_smile.gif

Nope sorry - that still leaves them in the "abridged" versions of the number
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

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
Back
Top