Hi, I'm wondering if there is a formula that will get a condition if a cell has less that 4 digits, to input extra spaces to add to 4 digits. E.g:

one column has:

4564

7898

4569

345

4567

87

3467What I need is that the formula inputs extra spaces so it will add make it a four digit "number" like this

4564

7898

4569

*345

4567

**87

3467

(where the asterisks are, I need spaces. This post won't take spaces before numbers, that's the reason for the asterisks)I need this to line up the numbers to the right after I concatenate those numbers and paste them in a text file for so an old program can import it.

Thanks.

George

Here's something that should work:

This assumes that there is at least one number in the cell.=IF(LEN(A1)=1,CHAR(32)&CHAR(32)&CHAR(32)&A1,IF(LEN(A1)=2,CHAR(32)&CHAR(32)&A1,IF(LEN(A1)=3,CHAR(32)&A1,A1)))

MIKE

or, to save work on your fingers: =REPT(" ",4-LEN(A1))&A1

Select the column, Go to Format, Cells, Custom and use ???? Best regards,

Bryan

Hi Bryco, Changing the formatting of the cell doesn't change the data.

If you have 123 in cell A1 and test its length =len(A1) you get 3.

Format the cell with ???? or 0000 and its appearance will change but len(A1) still returns 3.Regards

Thanks guys, works like a charm.

What works like a chram? You were offered various suggestions and it might help someone in the future if you tell us which solution worked for you.

Yes, you are right. Both solutions from Mike and Jon work the same way. This is practical when you have and old system in unix that needs a text file with the exact spaces to process. Thanks again.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History