macro to remove dot . between numbers and make numbers to 8 digits with ovetlapping empty r

RAM1972

Board Regular
Joined
Jun 29, 2014
Messages
217
Macro to remove dot (.) between numbers and the numbers to read 8 digits in a column still end of data with two zero at end

There are two types of data to convert in

Anyone can help

ht code
Expected results
ht codeExpected results
101.21010121004016.9340169300
101.29010129004016.9440169400
101.30010130004016.9540169500
101.9001019000
4016.9940169900
4017.0040170000
102.21010221004017.00
102.2901022900
4101.2041012000
102.31010231004101.5041015000
102.39010239004101.9041019000
102.9001029000

4102.1041021000
4102.2141022210

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,324
Hi,

Try multiplying them by 10000.

Oh, and if you need the leading zero, format them as 00000000.
 
Last edited:

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
I don't know that this is the best answer but it did work for me quickly.

Step 1: Change the HT Code Column to text format. For some reason this formula doesn't work with values ending in 0 it isn't not in text format; it pulls in the decimal. (I.E. 102.90)
Step 2: This Formula for the Left data set using a combination on Concatenate, Left and Right. Cell B4 is the reference to whichever cell the HT Code is in.

Code:
=+"0"&LEFT(B4,3)&RIGHT(B4,2)&"00"

And This formula for the right data set

Code:
=+LEFT(F4,4)&RIGHT(F4,2)&"00"

Where Cell F4 is the cell where the HT Code you want to convert is located.
 

RAM1972

Board Regular
Joined
Jun 29, 2014
Messages
217
Hi Meggesto

Tried both the second formula works well but I have some issues with the first formula

Get the following results formatted to text:confused:

Can you advise where i go wrong
ht codeExpected results
101.21
101.29
101.30
101.90
0
0
102.210
102.290
0
102.310
102.390
102.90

<tbody>
</tbody>
 

RAM1972

Board Regular
Joined
Jun 29, 2014
Messages
217

ADVERTISEMENT

Hi Rick

Worked well :)but the only drawback is that I get 000000:confused: when the cells are blanks.

Is there a solution to get rid of zeros as goes up to 20000 rows
 

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
I'd use RickXl's advice on this one actually he's idea was much cleaner.
You could try something like this

Code:
="0"&10000*(A1)

Where A1 is which ever cell your HT Code starts in. You wouldn't have to worry about converting the column to text or anything strange like that.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,256
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Heres a way:

=IF(LEN(A1)>0,TEXT(A1*10000,"00000000"),"")
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,324
Hi Rick

Worked well :)but the only drawback is that I get 000000:confused: when the cells are blanks.

Is there a solution to get rid of zeros as goes up to 20000 rows


Use a different Custom Format. Try:
00000000;;
The format before the first semi-colon is for positive numbers.
The format between the semi-colons is for negative numbers (I assume there will not be any?)
The format after the second semi-colon is for values of zero.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,913
Members
414,110
Latest member
docops

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