Break apart a string of 9 numbers and place each number in its own cell

[Kurt]

I have a set of 9 numbers like this in cell L7 000 000 500 050 005 550 505 055 555

What command should I use to break them apart and put them in cells L8, L9, L10, L11, L12, L13, L14, L15, L16?

I would think this might be a function but not sure.

Any ideas out there?

Again as always thanks in advance for any and all help!

 

[taurean]

One way would be to make the cell reference absolute so that the cell ref doesn't move like:

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>M7</TD><TD>=LEFT($L$7,3)</TD></TR><TR><TD>N7</TD><TD>=MID($L$7,5,3)</TD></TR><TR><TD>O7</TD><TD>=MID($L$7,9,3)</TD></TR><TR><TD>P7</TD><TD>=MID($L$7,13,3)</TD></TR><TR><TD>Q7</TD><TD>=MID($L$7,17,3)</TD></TR><TR><TD>R7</TD><TD>=MID($L$7,21,3)</TD></TR><TR><TD>S7</TD><TD>=MID($L$7,25,3)</TD></TR><TR><TD>T7</TD><TD>=MID($L$7,29,3)</TD></TR><TR><TD>U7</TD><TD>=RIGHT($L$7,3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
It will work (never mind my laziness of copying it across )

 

[Kurt]

Is there a way to create a macro to do this function?

I have a few hundred rows to do this.

For instance can I place my cursor in the cell that I want to break apart the string and use a macro.

Also thanks for your help taurean!

 

[Ron Coderre]

Try this
• Select your data cell....L7
• Data.Text-to-columns
...Check: Delimited..............Click: Next
...Check: Space..................Click: Next
...Set each column to: Text
...Select the destination cell (probably L7)
...Click: Finish

That will parse the source text at each space, placing each 3-char substring into cells across to the right

Note: you could start by selecting a single-column range of values and they would be parsed out to the right.

Is that something you can work with?

 

[Kurt]

Almost there Ron.

The problem now is I have numbers say 002 and yes I have formatted the source and target cells as Text to account for the zeros.

When I use the text to column methods it won't recognize the zeros even with the format.

 

[Ron Coderre]

Almost there Ron.

The problem now is I have numbers say 002 and yes I have formatted the source and target cells as Text to account for the zeros.

When I use the text to column methods it won't recognize the zeros even with the format.

Not sure what you mean by "won't recognize". Can you describe in a bit more detail?

 

[Kurt]

It won't display the zero when the number is a 002 or when a number begins with a "0".

 

[Ron Coderre]

It won't display the zero when the number is a 002 or when a number begins with a "0".

On the third screen of the Text-to-Columns...
• sequentially select each of the columns...and click: Text in the upper section.

That will prevent the Text-to-columns from overwriting the format you set.

Does that help?