Add Leading Spaces to Alphanumeric field

[sweetkel23]

I would like to add leading spaces to an alphanumeric field in order to keep the field 5 characters. I did post a question very similar to this yesterday, the difference is a different data source. This example does not contain leading zeros like yesterdays post did. I can't put expected output below as the mail will left justify if I show leading spaces. For example 45K would contain 2 leading spaces.

Data:
45K
118C
7A
36AN

Thank you!

[Ghozi Alkatiri]

Ie data at A1

the formula
=REPT("0";5-LEN(A1))&A1

[6StringJazzer]

I know of no way to format a text string within the cell to display with left padding, but this formula will do it for you in another column. If your data starts in A1, enter this in the desired column and copy down:

=REPT(" ",5-LEN(A1))&A1

You will get an error if the cell has more than 5 characters to start with.

[sweetkel23]

The formula from 6StringJazzer worked fine after I did a trim on the entire column. Thank you!