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

[RAM1972]

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>

 

[RickXL]

Hi,

Try multiplying them by 10000.

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

 

[Meggesto]

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.

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

And This formula for the right data set

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

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

 

[RAM1972]

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]

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]

I'd use RickXl's advice on this one actually he's idea was much cleaner.
You could try something like this

="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]

Heres a way:

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

 

[RoryA]

Crossposted here: macro to remove dot . between numbers and make numbers to 8 digits with overlapping empty rows | Chandoo.org Excel Forums - Become Awesome in Excel

 

[RickXL]

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.