Format 7 digit number into a price (e.g. 0001187 --> 1.19)

**bridawn** · 05-27-2016, 03:00 PM

I have a long column of numbers that are listed as 7 digits. All start with 0's but some have two 0's, some have three, etc.

How can I set the formatting so that they all become a price based off the first number following the 0's?

So I have 0002865 and I want that to change to $2.87 (2.865 rounded to two decimal places, basically).

I do not want it to change 0002865 to be $2865.00 (which is what it's doing now when I try).

Thanks

**jayherring86** · 05-27-2016, 03:06 PM

Hello,

I think you need to use this:

Please Login or Register  to view this content.

This formula turned my test number, 0002685 into $2.69

**bridawn** · 05-27-2016, 03:13 PM

Thanks. Where do i put that formula? I'm trying to use the "Format Cells" one where it's things like 0.00 or #.###, etc. but that formula doesn't work in there?

Also, will that work the same for numbers with only two leading 0's? I.e. 0027029 would be $2.70?

**newdoverman** · 05-27-2016, 03:33 PM

You could enter a 1 somewhere on the worksheet and copy it. Select the values go to Paste Special and in the Operation, select Multiply and click OK. Then in B1 enter this formula and format as currency. This will get rid of all leading zeros and return as real numbers the values that the text numbers represented.

Formula:

Please Login or Register  to view this content.

	A	B
1	2865	$2.87
2	3945	$3.95
3	1234	$1.23
4	5678	$5.68

If the text values are being recognized as Numbers Stored as Text then simply use the above formula in column B, and format as currency.

	A	B
1	0002865	$2.87
2	0003945	$3.95
3	0001234	$1.23
4	0005678	$5.68

**jayherring86** · 05-27-2016, 03:51 PM

My original formula was meant to be pasted in a cell on the worksheet.

This new one:

Please Login or Register  to view this content.

deals with 5 digit (two leading zero) numbers and 4 digit (3 leading zero) numbers. Just paste it in the cell where you want the dollar value.

Doverman's solution is equally valid, though you could achieve the same thing by using the formula "=value(a1)"

**bridawn** · 05-27-2016, 03:52 PM

Thank you, that worked!

Format 7 digit number into a price (e.g. 0001187 --> 1.19)

LinkBack

Thread Tools

Rate This Thread

Display

Format 7 digit number into a price (e.g. 0001187 --> 1.19)

Re: Format 7 digit number into a price (e.g. 0001187 --> 1.19)

Re: Format 7 digit number into a price (e.g. 0001187 --> 1.19)

Re: Format 7 digit number into a price (e.g. 0001187 --> 1.19)

Re: Format 7 digit number into a price (e.g. 0001187 --> 1.19)

Re: Format 7 digit number into a price (e.g. 0001187 --> 1.19)

Thread Information

Users Browsing this Thread

Similar Threads

Convert 3 digit or 4 digit number to time format

Format 6 digit number as YYYYMM

[SOLVED] Converting a 4 digit number into Time Format

Five-digit Number Format Codes

[SOLVED] How do i format a 5 to 6 digit number into the correct date?

[SOLVED] How do i format a 5 to 6 digit number into the correct date?

[SOLVED] How do i format a 5 to 6 digit number into the correct date?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions