This is a discussion on Text padding within the Excel Questions forums, part of the Question Forums category; How can I convert: SG99 to SG9 9 ie pad to 6 must work also for SG999 to SG99 9 ...

How can I convert:

SG99

to

SG9 9 ie pad to 6

must work also for

SG999

to

SG99 9

Regards

See if this will do the trick.....

if SG99 were to be in A1, put this formula in B1
=LEFT(A1,LEN(A1)-1)&" "&RIGHT(A1)

3. Cheers dude you guys rock

4. cheers but does not work for

NP113

which should be

NP11 3

5. Hi!
I suspect a space after 3.
try this.

Code:
`=LEFT(trim(A1),LEN(trim(A1))-1)&" "&RIGHT(trim(A1))`

You know Sixsense I almost added the Trim function just for that reason, but as you can see didn't. I guess that should be a standard for all such formulas huh!

Another simple way using REPLACE
Eli

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl97 : OS = Windows 98
 File Edit View Insert Options Tools Data Window Help About
 B1B2B3 =

A
B
C
D
E
1
GS99GS9*9***
2
GS999GS99*9***
3
NP113NP11*3***
4
*****
5
*****
6
*****
7
*****
8
*****
9
*****
10
*****
11
*****
 Sheet1 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Originally Posted by ken2step
You know Sixsense I almost added the Trim function just for that reason, but as you can see didn't. I guess that should be a standard for all such formulas huh!
Maybe ken.
But personaly, I dont like to see that extra checking. Maybe in VB because it is not seen. It's a sore of the eye for me.

I agree but with data input, comes typo errors usually. I guess adding a Validation to the field could help also.

Also:

=REPLACE(A1,LEN(A1),0," ")

or, with some control...

=IF(A1<>"",REPLACE(A1,LEN(A1),0," "),"")

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•