# text formatting

#### mattmickle

##### Board Regular
Value = \$7,564,264.00

I need this to become a sixteen character text string:

0000000756426400

my formula =Text(Text(substitute(B13,".",""),"0000000000000000"

becomes 000000007564264

this works if, for example, the number is \$5,469,062.72, but for any round number, it drops the decimal zeroes.

the last 2 digits should be implied decimals. How can i keep them?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
With
A1: \$7,564,264.00

This regular formula returns the string you're looking for:
Code:
``B1:[/COLOR]=TEXT(--SUBSTITUTE(TEXT(A1,"0.00"),".",""),"0000000000000000")``
This also seems to work:
Code:
``B1: =TEXT(A1*100,"0000000000000000")``

In those examples, B1 evaluates to: "0000000756426400"

Is that something you can work with?

Last edited:
Try this:
Code:
``=SUBSTITUTE(TEXT(B13,"00000000000000.00"),".","")``

Joe4,
That worked! But, I figured out an easier way to accomplish it.

instead of substituting for the".", i just multiply the value times 100 in my Text formulas.
=Text(b13*100,"0000000000000000")

That worked! But, I figured out an easier way to accomplish it.

instead of substituting for the".", i just multiply the value times 100 in my Text formulas.
=Text(b13*100,"0000000000000000")
Yep, that works too.

That is one of the cool things about Excel. There is often multiple solutions to the same problem.

Replies
1
Views
121
Replies
2
Views
61
Replies
1
Views
144
Replies
3
Views
178
Replies
8
Views
262

1,196,280
Messages
6,014,426
Members
441,818
Latest member
itsfaisalkhalid

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