Spread data and keep format
 

I have some strings in column A that appear like:

123.123.1234.123456.123.12.123
000.000.0000.000000.000.00.000
123.456.0000.987654.444.00.222

They all follow the same pattern. I want to keep column A intact, but spread
the values across seven columns to the right. Like so (the pipes below denote
columns):

B C D E F G H
123 | 000 | 1234 | 123456 | 999 | 00 | 95

Note that I need to keep the requisite number of zeros after the spread, so
text-to-columns fails, since '000' translates as '0'.

I would like a speadsheet function and/or VBA code that would do this
correctly. Many thanks for your assistance.

Spread data and keep format
 

DataText to Columns will not fail when done correctly.

Select the range in column A

DataText to ColumnsDelimitedOther enter a periodNextSelect all columns by
Shift + Click and Column Data FormatText

Destination....select a cell...say B1........ and Finish


Gord Dibben MS Excel MVP

On Fri, 4 Jan 2008 13:16:07 -0800, XP wrote:

I have some strings in column A that appear like:

123.123.1234.123456.123.12.123
000.000.0000.000000.000.00.000
123.456.0000.987654.444.00.222

They all follow the same pattern. I want to keep column A intact, but spread
the values across seven columns to the right. Like so (the pipes below denote
columns):

B C D E F G H
123 | 000 | 1234 | 123456 | 999 | 00 | 95

Note that I need to keep the requisite number of zeros after the spread, so
text-to-columns fails, since '000' translates as '0'.

I would like a speadsheet function and/or VBA code that would do this
correctly. Many thanks for your assistance.

Spread data and keep format
 

On Jan 5, 8:16 am, XP wrote:
I have some strings in column A that appear like:

123.123.1234.123456.123.12.123
000.000.0000.000000.000.00.000
123.456.0000.987654.444.00.222

They all follow the same pattern. I want to keep column A intact, but spread
the values across seven columns to the right. Like so (the pipes below denote
columns):

B C D E F G H
123 | 000 | 1234 | 123456 | 999 | 00 | 95

Note that I need to keep the requisite number of zeros after the spread, so
text-to-columns fails, since '000' translates as '0'.

I would like a speadsheet function and/or VBA code that would do this
correctly. Many thanks for your assistance.

In B1 use the formula...

="." & A1

filled down to add a "." to the start of each string.
Then use Edit|Replace to replace each "." with ".a"
Then use Data|Text to Columns with "." as the delimiter
This produces the correct result except they all have "a" as a prefix.
Use =MID(B1,2,LEN(B1)-1) filled across for a total of 7 columns then
down the sheet to get rid of the "a" prefix.

When I did this the 0000s stayed.

Ken Johnson

Spread data and keep format
 

I wish I had seen Gord's before I submitted my crap(:-\

Ken Johnson