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

#### RAM1972

##### Board Regular
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 code Expected results 101.21 01012100 4016.93 40169300 101.29 01012900 4016.94 40169400 101.30 01013000 4016.95 40169500 101.90 01019000 4016.99 40169900 4017.00 40170000 102.21 01022100 4017.00 102.29 01022900 4101.20 41012000 102.31 01023100 4101.50 41015000 102.39 01023900 4101.90 41019000 102.90 01029000 4102.10 41021000 4102.21 41022210

<tbody>
</tbody>

Last edited:

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### RickXL

##### MrExcel MVP
Hi,

Try multiplying them by 10000.

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

Last edited:

#### Meggesto

##### Board Regular
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
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

Can you advise where i go wrong
 ht code Expected results 101.21 101.29 101.3 0 101.9 0 0 0 102.21 0 102.29 0 0 102.31 0 102.39 0 102.9 0

<tbody>
</tbody>

#### RAM1972

##### Board Regular

Hi Rick

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

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

#### Meggesto

##### Board Regular
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

Heres a way:

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

#### RickXL

##### MrExcel MVP
Hi Rick

Worked well but the only drawback is that I get 000000 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.

Replies
3
Views
646

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,514
Messages
5,832,192
Members
430,114
Latest member
kefier

### 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.

### Which adblocker are you using?

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

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