Format column cell to be 6 characters long 5

[lanm]

I have a sheet with a single column, but there are about 3700 rows. Some are 4 to 5 characters long. I need them all 6 char long.

Below is kinda what I need to do (add zeros to the left side to make the cells 6 chars long):

If Len(x) = 4 then "00"+X
Else
If len(x) = 5 then "0"+X
End If

 

[Tiglet]

Assuming Data is in Col A (starting at Row 1), then put this formula into Col B.
NB.This will only resolve data that is 4 & 5 characters long. Everything else is assumed to be 6 characters

=IF(LEN(A1)=4,"00"&A1,IF(LEN(A1)=5,"0"&A1,A1))

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[SkipVought]

Hi,

If your string is in A1
[tt]
=REPT("0",6-LEN(A1))&A1
[/tt]


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Tiglet]

Skip,

Neat! and solves the "only picks up 4 & 5 characters long" of mine. I love it!



Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[AnotherHiggins]

Without the use of a helper cell, you can just change the formatting of the cell:
Highlight the column, go to Format > Cells, then on the Number tab, choose Custom on the list on the left and type 00000 in the box on the top right.

NOTE: This will only change the appearance of the data, it won't actually change the data contained in the cells.

If you really need to change the data, then you were on the right track. Go with Skip's suggestion.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[lanm]

Thanks to all!
Yeah, I did try the Format>Cells and did Custom with "000000". Once I pulled this sheet over to SQL Server via DTS, I did notice the formatting went back to it's original way. I should have used the rept().

I ended up writing a small routine to check for len(4) or len(5) and add zeros as needed.

Thanks again! Great info!

 

[lanm]

Hey...dumb question, but if I've got 14242 rows in this sheet, how can I get the formula to go down to the 14242nd row w/o dragging and holding the mouse?

Thanks again! rept() is a nice function!

 

[RoyVidar]

Go to the bottom of the column containing the data (ctrl+downarrow). Move cursor to the cell (column) in the same row, select to top (shift+ctrl+uparrow) ensure also the cell containing the formula is selected (might need an additional uparrow, while holding shift+ctrl). Then hit ctrl+D (Fill Down) - you'll find most of these thingies through stydying the menus while working ;-)

Roy-Vidar

 

[AnotherHiggins]

Hover the cursor over the bottom right corner of the cell until it changes to the little "+" (like you would if you were going to drag 'n' fill), then double click. This will fill down as far as the adjacent column is contiguously filled.

Or you can go to column A,
[Ctrl]+[Down Arrow] (this takes you to the end of the current section of data)
[right arrow key] or [ignore][Tab][/ignore] go one cell to the right
[Ctrl]+[Up Arrow] Goes up to the first occupied cell
[Ctrl]+[D] Keyboard shortcut for FillDown.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Copy the formula.

Select a cell in a column that has no empty rows of data

[End] [Down Arrow]

select the column containing the formula

[Shift]+([End] [Up Arrow]) Selects the range to paste into

Edit/Paste

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[lanm]

Got it! Thanks again for the help!

 

[SkipVought]

As John explained....

FORMAT just changes the APPEARANCE and NOT the DATA.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]