Formula with spaces

Microsoft Excel 2007
October 7, 2009 at 15:43:48
Specs: Windows Vista
 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:4564789845693454567873467What I need is that the formula inputs extra spaces so it will add make it a four digit "number" like this456478984569*3454567**873467(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

See More: Formula with spaces

#1
October 7, 2009 at 19:06:10
 Simply right justify doesn't work?Would adding Zero's work?MIKE

Report •

#2
October 7, 2009 at 19:27:39
 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)))MIKEhttp://www.skeptic.com/

Report •

#3
October 8, 2009 at 01:20:37
 or, to save work on your fingers:=REPT(" ",4-LEN(A1))&A1

Report •

Related Solutions

#4
October 8, 2009 at 04:47:43
 Select the column, Go to Format, Cells, Custom and use ????Best regards,Bryan

Report •

#5
October 8, 2009 at 04:54:51
 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

Report •

#6
October 8, 2009 at 08:18:12
 Thanks guys, works like a charm.

Report •

#7
October 8, 2009 at 08:33:05
 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.

Report •

#8
October 8, 2009 at 09:25:44
 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.

Report •