formatting x-xxxx as x-xxxx.00

Hi, welcome to the forum

it doesn't seem to work with numbers that have a dash.

thats because they are not numbers, they are text. I dont think you can use formatting like that, on text

You cannot change the contents of a cell with formatting. 1 way around this would be to use a helper column to remove the - You then either just use that helper column, or copy/paste values over the original date

if you are not averse to using a helper column (which you could copy >> paste special >> values over your original) you could use something like this... =IF(LEN(A1)<7,A1&".00",IF(LEN(A1)<9,A1&"0",A1)) assuming your values are in column A.

If the contents of column A are text numbers with the dashes some with .00 at the end and some without, this will convert all of them to have .00 at the end.

Formula:

Please Login or Register  to view this content.

RESULT

	A	B
2	12-1234	12-1234.00
3	234-4567.00	234-4567.00
4	41-1234	41-1234.00
5	65.4321	65.4321.00

With right alignment:

	A	B
2	12-1234	12-1234.00
3	234-4567.00	234-4567.00
4	41-1234	41-1234.00
5	65.4321	65.4321.00

A short one

=SUBSTITUTE(A1,"-","-")&".00"

Row\Col	A	B
1	4-5678	4-5678.00
2	45678	45678.00
3	4-1268	4-1268.00
4	3-6578	3-6578.00
5	43245	43245.00

@AlKey

Your formula handles all the entries that don't already end in .00 but adds .00 to those entries that already end in .00 giving x-xx.00.00

I guess I didn't read it carefully

Here is another one.

=SUBSTITUTE(SUBSTITUTE(A1,"-","-"),".00","")&".00"

Row\Col	A	B
1	4-5678	4-5678.00
2	45678	45678.00
3	4-1268	4-1268.00
4	3-6578	3-6578.00
5	43245	43245.00
6	x-xxxx.00	x-xxxx.00