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:
4564
7898
4569
345
4567
87
3467

What 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


See More: Formula with spaces

Report •


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

MIKE

http://www.skeptic.com/


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

MIKE

http://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 •


Ask Question